Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have recently started using PowerBI.
Please help me with this DAX calculation:
The available quantity of a product is logged in a table every time it's changing.
stock level
product date quantity
A 2020-01-01 1
B 2020-01-03 10
B 2020-02-02 15
A 2020-02-03 0
desired result: a calendar showing stock level (=quantity of latest entry per product <= date) daily
product date stock level
A 2020-01-01 1
B 2020-01-01 0
A 2020-01-02 1
B 2020-01-02 0
A 2020-01-03 1
B 2020-01-03 10
... ... ...
A 2020-02-25 0
B 2020-02-25 15
.. .. ..
What I tried:
made a date table "Date"
made these Measures:
overall Quantity = SUM(stock[quantity])
quantity per day = CALCULATE([overall Quantity],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))
OK, this sums up quantity. Not quite what I want.
Thanks for your help
Solved! Go to Solution.
You're moving in the right direction with the date table. Do not connect that table to your data model (or make all connections inactive, or use CROSSFILTER(,,none) for a cartesian product).
What you need to do next is for each date from that table and each product to calculate a measure of the latest inventory. If you want to arrive at the desired output that you indicate then I would recommend creating another lookup table
stock =
VAR p =
MAX ( Output[product] )
VAR d =
MAX ( Output[Date] )
VAR a =
CALCULATE (
MAX ( Inventory[date] ),
Inventory[date] <= d,
Inventory[product] = p
)
VAR s =
IF (
ISBLANK ( a ),
0,
CALCULATE (
MAX ( Inventory[quantity] ),
Inventory[date] = a,
Inventory[product] = p
)
)
RETURN
s
Convert your selectedvalue into a single row/single column table and then do the cross join against that.
Computationally there will be no real benefit though.
7000x365 is 25 lakh - it is what it is.
Don't use 365 days. Compute weekly or monthly stock levels. Don't use measures.
I do similar computations over much larger date ranges and unit counts, and I do all these computations in my data source. The stock level of product A on last friday doesn't really change any more after the fact, so recomputing it in a Power BI measure is counterproductive, to say it nicely.
Hello,
is there a chance to do a "virtual crossjoin" for Date and one Product in the measure?
My idea is to output stock level history for the currently selected product only.
In the hope to get rid of that large table Output=CROSSJOIN(Dates,Products)
tried this - but it is not working yet ("Output" is not allowed in this context...)
stock_current_product =
VAR selProduct=SELECTEDVALUE('Inventory'[product]) /*dropdown*/
VAR Output=CROSSJOIN('Dates',FILTER(VALUES(Inventory[product]),Inventory[product]=selProduct))
VAR p =
MAX ( Output[product] )
VAR d =
MAX ( Output[Date] )
VAR a =
CALCULATE (
MAX ( Inventory[date] ),
Inventory[date] <= d,
Inventory[product] = p
)
VAR s =
IF (
ISBLANK ( a ),
0,
CALCULATE (
MAX ( Inventory[quantity] ),
Inventory[date] = a,
Inventory[product] = p
)
)
RETURN
s
You're moving in the right direction with the date table. Do not connect that table to your data model (or make all connections inactive, or use CROSSFILTER(,,none) for a cartesian product).
What you need to do next is for each date from that table and each product to calculate a measure of the latest inventory. If you want to arrive at the desired output that you indicate then I would recommend creating another lookup table
stock =
VAR p =
MAX ( Output[product] )
VAR d =
MAX ( Output[Date] )
VAR a =
CALCULATE (
MAX ( Inventory[date] ),
Inventory[date] <= d,
Inventory[product] = p
)
VAR s =
IF (
ISBLANK ( a ),
0,
CALCULATE (
MAX ( Inventory[quantity] ),
Inventory[date] = a,
Inventory[product] = p
)
)
RETURN
s
Hey, great answer on the topic.
I'm actually trying to achieve the same result, but I'm struggling in implementing your solution as my stock is always equal to 1.
My 'Stock Level' is your Inventory, and I followed your logic for Products, Dates and Output, but I keep getting all 1 on the stock column.
Think you could help me out? This is a bit beyond my expertise I admit.
Here is my sample file:
https://drive.google.com/file/d/1UWVSCX03KW7LXU8YaRvgYVreMHhSLD_x/view?usp=sharing
Thank you very much!!! For my test data it works perfectly.
But 7.000 products and a date range of 365 days make calculations very very slow.
Do you have any ideas how to optimize it?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |