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 there,
I have an InventoryForecast table for which I successfully calculate a Running Total/Cumulative measure by using the EndDate for each forecasted item. Code as follows:
Forecast Cost Cumulative:= CALCULATE ( SUMX('Inventory Planning', 'Inventory Planning'[Qty] * 'Inventory Planning'[Cost]), FILTER ( ALL (' Inventory Planning'[EndDate] ), 'Inventory Planning'[EndDate] <= MAX ('Inventory Planning'[EndDate] ) ) )
I then have a DateKey ('dimCalendar') for which my 'Inventory Planning' table has a relationship for EndDate to the dimCalendar[Date].
While I understand that the cumulative measure uses EndDate from it's same table to perform the running sum, if I wish to enrich my report by instead using columns from my dimCalendar (such as Month Name, Fiscal periods etc), the measure does not produce a running total and merely reflects the individual value for that month, as per the below screenshot.
Also, with an additional date field in my report, end users will continue to be fconfused and will very likely not use the "corect" EndDate field and when using the DateKey fields will end up with wrong results.
The question then is whether there is any means by which I can effectively hide the EndDate from the users so they select only from the DateKey fields, while still preserving the inegrity of my runnign sum calculation?
Solved! Go to Solution.
Hi,
Drag the Date from the Calendar Table and write the following measure:
=CALCULATE(SUMX('Inventory Planning','Inventory Planning'[Qty]*'Inventory Planning'[Cost]),DATESBETWEEN('dimCalendar'[Date],MINX(ALL('dimCalendar'),'dimCalendar'[Date]),MIN('dimCalendar'[Date])))
Hope this helps.
Hi,
Drag the Date from the Calendar Table and write the following measure:
=CALCULATE(SUMX('Inventory Planning','Inventory Planning'[Qty]*'Inventory Planning'[Cost]),DATESBETWEEN('dimCalendar'[Date],MINX(ALL('dimCalendar'),'dimCalendar'[Date]),MIN('dimCalendar'[Date])))
Hope this helps.
Thanks @Ashish_Mathur, this does in deed work well and also retains the relatiosnship with my Vendor table for further slicing.
You are welcome.
Hi @wi11iamr
You may try below measure:
Forecast Cost Cumulative:= CALCULATE ( SUMX('Inventory Planning', 'Inventory Planning'[Qty] * 'Inventory Planning'[Cost]), FILTER ( ALL (' Inventory Planning' ), 'Inventory Planning'[EndDate] <= MAX ('Inventory Planning'[EndDate] ) ) )
Regards,
Cherie
Ah, beautiful, thanks @v-cherch-msft! While I see what you've done by removing the [EndDate] from the filter context, I admittedly don't fully comprehend the logic in doing so. If you have a minute, would you mind clarifying so I may better understand for future code?
That said, while the Running Sum now works well at an aggregated level, when I slice by another dimension, such as vendor. then each vendor has the exact same running total sum as the overall total. Any thoughts on how to remedy this?
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |