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 3 tables:
On my report I have a date slicer from calendar where I can select a single date. Then I have a table visual that displays ALL products, regardless if they have a quantity on the selected date. But instead of a blank quantity, I want to display what the previous quantity was.
Here's the quantity by date:
And products:
My report looks like this:
As you can see, Product 1 has blank quantity because no data for selected date is available. Previous quantity should display 24, which is the quantity on 01/07/2021. The (useless) DAX I used for the previous quantity is:
Previous quantity = IF(
ISBLANK('Quantity by date'[Quantity]),
CALCULATE(
MAX('Quantity by date'[Quantity]),
FILTER(
ALL('Quantity by date'),
'Quantity by date'[Product ID] = EARLIER('Quantity by date'[Product ID])
)
),
CALCULATE(
MAX('Quantity by date'[Quantity]),
FILTER(
ALL('Quantity by date'),
'Quantity by date'[Date] < EARLIER('Quantity by date'[Date])
&& 'Quantity by date'[Product ID] = EARLIER('Quantity by date'[Product ID])
)
)
)
Here's the PBIX file:
https://drive.google.com/file/d/1tMBMXGhk2jt76pakOgzfEoNf0N1leql1/view?usp=sharing
Solved! Go to Solution.
Hi @JKoivu
You can use a measure to get the result.
Previous quantity =
VAR __previousDate =
CALCULATE (
MAX ( 'Quantity by date'[Date] ),
FILTER (
ALL ( 'Quantity by date' ),
'Quantity by date'[Date] < MAX ( 'Calendar'[Date] )
&& 'Quantity by date'[Product ID] = MAX ( 'Products'[Id] )
)
)
RETURN
CALCULATE (
SUM ( 'Quantity by date'[Quantity] ),
FILTER (
ALL ( 'Quantity by date' ),
'Quantity by date'[Date] = __previousDate
&& 'Quantity by date'[Product ID] = SELECTEDVALUE ( 'Products'[Id] )
)
)
If you calculate previous quantity in a calculated column, you should also use a measure to get the corresponding previous quantity column value when you hope to use a slicer to switch it.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @JKoivu
You can use a measure to get the result.
Previous quantity =
VAR __previousDate =
CALCULATE (
MAX ( 'Quantity by date'[Date] ),
FILTER (
ALL ( 'Quantity by date' ),
'Quantity by date'[Date] < MAX ( 'Calendar'[Date] )
&& 'Quantity by date'[Product ID] = MAX ( 'Products'[Id] )
)
)
RETURN
CALCULATE (
SUM ( 'Quantity by date'[Quantity] ),
FILTER (
ALL ( 'Quantity by date' ),
'Quantity by date'[Date] = __previousDate
&& 'Quantity by date'[Product ID] = SELECTEDVALUE ( 'Products'[Id] )
)
)
If you calculate previous quantity in a calculated column, you should also use a measure to get the corresponding previous quantity column value when you hope to use a slicer to switch it.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Qty = SUM( 'Quantity by date'[Quantity] )
Prev Qty =
CALCULATE( [Qty], PREVIOUSDAY( 'Calendar'[Date] ) )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL Thanks for your reply, this works great on this example! Though I forgot to mention that there can be gaps of several days, so the previous quantity could be from many days ago, not just previous. Any tips on how to achieve that?
I still haven't figured this out, can anyone help me?
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |