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 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).
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |