Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
Let's say I have inventory table with the following data:
Jan 2017: 10
Feb 2017: 20
Mar 2017: 30
and then I want to create a Date by Inventory Quantity matrix/bar chart that continues to populate the numbers from Apr 2017 onwards with the last available inventory value (i.e. 30, from Mar 2017). The expected output is as follows:
Jan 2017: 10
Feb 2017: 20
Mar 2017: 30
Apr 2017: 30
...
Nov 2017: 30
Dec 2017: 30
How do I write the DAX measure? I've tried the following but it doesn't work.
Max PInv Value = IF(ISBLANK(SUM(InventoryPartial[PInvQuantity]))=FALSE(), SUM(InventoryPartial[PInvQuantity]), CALCULATE(SUM(InventoryPartial[PInvQuantity]), FILTER(all(InventoryPartial), InventoryPartial[Date] = MAX(InventoryPartial[Date]))))
Here's a link to the sample file. Please use the InventoryPartial table.
Thank you!
Solved! Go to Solution.
Hi @Anonymous,
You could try this measure below.
Max PInv Value = VAR vdate = CALCULATE ( LASTNONBLANK ( 'InventoryPartial'[Date], MAX ( 'InventoryPartial'[Date] ) ), ALL ( DateTable ) ) RETURN IF ( ISBLANK ( SUM ( InventoryPartial[PInvQuantity] ) ) = FALSE (), SUM ( InventoryPartial[PInvQuantity] ), CALCULATE ( SUM ( InventoryPartial[PInvQuantity] ), FILTER ( ALL ( InventoryPartial ), MONTH ( InventoryPartial[Date] ) = MONTH ( vdate ) ) ) )
And the output is below.
Hope it can help you!
Best Regards,
Cherry
Hi @Anonymous,
You could try this measure below.
Max PInv Value = VAR vdate = CALCULATE ( LASTNONBLANK ( 'InventoryPartial'[Date], MAX ( 'InventoryPartial'[Date] ) ), ALL ( DateTable ) ) RETURN IF ( ISBLANK ( SUM ( InventoryPartial[PInvQuantity] ) ) = FALSE (), SUM ( InventoryPartial[PInvQuantity] ), CALCULATE ( SUM ( InventoryPartial[PInvQuantity] ), FILTER ( ALL ( InventoryPartial ), MONTH ( InventoryPartial[Date] ) = MONTH ( vdate ) ) ) )
And the output is below.
Hope it can help you!
Best Regards,
Cherry
Hi Cherry @v-piga-msft, do you mind explaining the formula? The LASTNONBLANK variable really tripped me off, I wouldn't have thought of it. (I'm still relatively new to DAX).
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |