Mar 03 2021 07:08 AM
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
Mar 03 2021 07:30 AM
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.