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.
I have a standard Date table, Sales table, and Incoming table.
The Sales Table and Incoming Table both have QTY, and a Sales_Date for sales, and an Expected_Date for Incoming.
I plot daily sales on a line chart, with a measure that simply sums Sales_QTY (Sales_QTY_SUM). I don't have to do anything else, if Date is on the axis, I get what I want, for sales. I also have a measure that sums Incoming_QTY (Incoming_QTY_SUM), and it works the same way, but that is not exactly what I want.
I want to see Incoming_QTY as a cumulative total going forward from Expected_Date. So if an incoming record has a Date of March 1st, I want to see 0 before March 1st, and Incoming_QTY in the line chart results starting on March 1st, then on March 2nd, and March 3rd, ..
Date[Date] and Incoming[Expected_Date) are both columns, and trying to use them in an IF statement fails because I am not using any aggregates.
IF([Expected_Date]<=[Date],Incoming_QTY,0) is the logic.
How do end up with a measure that will plot this properly on a line chart?
Thanx
Phil
Solved! Go to Solution.
Try this slight modification...
Cumulative Incoming = CALCULATE( SUM(Incoming[Incoming Qty]) , FILTER( ALLSELECTED('Incoming'), 'Incoming'[Incoming Date]<=MAX('Dates'[Date]) ) )
Sounds like you are just after a cumulative measure similar to this :
Cumulative Incoming = CALCULATE( SUM(Incoming[Incoming Qty]) , FILTER( ALL('Incoming'), 'Incoming'[Incoming Date]<=MAX('Dates'[Date]) ) )
I have a PBIX file you can try it with here https://1drv.ms/u/s!AtDlC2rep7a-kHTghFw8Upt_GDbN
Awesome! So I have this:
IncomingQTYCumulative = CALCULATE(SUM(IncomingInventories[order_qty]),FILTER(ALL(IncomingInventories),'IncomingInventories'[expected_date]<=MAX('Date'[Date])))
And while it works perfectly, these inventory numbers are by Item_ID, relating to 'item[Item_id] and this measure is immune to slicers tied to the Item table. I know why, because of the ALL(IncomingInventories), but I need to be able to slice by 'Item[Stat_ID].
I can hardcode it into the measure, but that is not a good solution. How can I allow the slicer to carry through?
Thanx
Try this slight modification...
Cumulative Incoming = CALCULATE( SUM(Incoming[Incoming Qty]) , FILTER( ALLSELECTED('Incoming'), 'Incoming'[Incoming Date]<=MAX('Dates'[Date]) ) )
That seems to work perfectly. Thank you very much!
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |