Date formule to extra date with powerQuery from file

%3CLINGO-SUB%20id%3D%22lingo-sub-2181856%22%20slang%3D%22en-US%22%3EDate%20formule%20to%20extra%20date%20with%20powerQuery%20from%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2181856%22%20slang%3D%22en-US%22%3E%3CP%3EAll%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20file%20with%20a%20lot%20of%20transactions%20within%20a%20warehouse.%20With%20Power%20Query%20in%20Excel%2C%20I%20try%20to%20get%20a%20good%20view%20on%20all%20the%20transactions%20%2F%20data.%3C%2FP%3E%3CP%3EIf%20I%20summarize%20the%20quantity%20of%20products%20since%20the%20beginning%20of%20the%20database%20(summarize%20all%20the%20different%20transactions%20such%20as%20buy%2C%20sell%2C%20scrap%2C...)%2C%20then%20I%20get%20the%20current%20stock%20amount.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20see%20is%20what%20the%20stock%20was%20on%20a%20given%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20managed%20with%20the%20following%20trick%20to%20get%20the%20data%20from%20a%20cell%20in%20Excel%20into%20my%20Power%20Query%20formula%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F26989279%2Fhow-can-i-reference-a-cells-value-in-powerquery%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EReference%20a%20cell's%20value%20in%20PowerQuery%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20for%20me%20now%20is%3A%20it's%20a%20Date%20field...%20and%20I%20just%20don't%20get%20it%20managed%20to%20get%20the%20date.%3C%2FP%3E%3CP%3EFirst%20I%20tested%20the%20formula%20without%20reference%20to%20the%20cell%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EStock%20given%20date%20v1%3A%3DCALCULATE(%5BGoods%20Received%5D-%5BReady%20For%20Shipment%5D%2B%5BStock%20Correction%5D%2B%5BStock%20Correction%20(Damage)%5D%2B%5BStock%20Correction%20(Scrap)%5D%2B%5BProduction%5D%2B%5BOptimization%20Deviation%5D%2B%5BGoods%20Received%20Rollback%5D-%5BOvershipment%20Notice%5D%2C%0A%20FILTER(ALL(dCalendar)%2C%0A%20%20dCalendar%5BDate%5D%20%26gt%3B%3D%20MIN(dCalendar%5BDate%5D)%20%26amp%3B%26amp%3B%20%0A%20%20dCalendar%5BDate%5D%20%26lt%3B%20%20%20DATE(VALUE(2021)%2CVALUE(01)%2CVALUE(04))%0A%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENow%20I%20likte%20to%20replace%20the%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20%20dCalendar%5BDate%5D%20%26gt%3B%20DATE(VALUE(2020)%2CVALUE(06)%2CVALUE(06))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewith%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EdCalendera%5BDate%5D%20%26gt%3B%20MAX('Invoked%20FunctionGetValue'%5BInvoked%20FunctionGetValue%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20do%20I%20do%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDennis%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2181856%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2181922%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20formule%20to%20extra%20date%20with%20powerQuery%20from%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2181922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F539963%22%20target%3D%22_blank%22%3E%40DennisDp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20mix%20Power%20Query%20and%20DAX%20with%20data%20model%2C%20these%20are%20different%20tools.%3C%2FP%3E%0A%3CP%3EIf%26nbsp%3B'Invoked%20FunctionGetValue'%20is%20the%20table%20in%20data%20model%20you%20may%20use%20VAR%20to%20calculate%20variable%20with%20max%20date%20for%20that%20table%20and%20after%20that%20use%20that%20variable%20in%20filter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

All,

 

I have a file with a lot of transactions within a warehouse. With Power Query in Excel, I try to get a good view on all the transactions / data.

If I summarize the quantity of products since the beginning of the database (summarize all the different transactions such as buy, sell, scrap,...), then I get the current stock amount.

 

What I want to do see is what the stock was on a given date.

 

So I managed with the following trick to get the data from a cell in Excel into my Power Query formula:

Reference a cell's value in PowerQuery 

 

The problem for me now is: it's a Date field... and I just don't get it managed to get the date.

First I tested the formula without reference to the cell:

 

Stock given date v1:=CALCULATE([Goods Received]-[Ready For Shipment]+[Stock Correction]+[Stock Correction (Damage)]+[Stock Correction (Scrap)]+[Production]+[Optimization Deviation]+[Goods Received Rollback]-[Overshipment Notice],
	FILTER(ALL(dCalendar),
		dCalendar[Date] >= MIN(dCalendar[Date]) && 
		dCalendar[Date] <   DATE(VALUE(2021),VALUE(01),VALUE(04))
	)
)

Now I likte to replace the

		dCalendar[Date] > DATE(VALUE(2020),VALUE(06),VALUE(06))

with

dCalendera[Date] > MAX('Invoked FunctionGetValue'[Invoked FunctionGetValue])

 

what do I do wrong?

 

Thanks,

Dennis

 

1 Reply

@DennisDp 

You mix Power Query and DAX with data model, these are different tools.

If 'Invoked FunctionGetValue' is the table in data model you may use VAR to calculate variable with max date for that table and after that use that variable in filter.