How to show most recent Calculated Value Base on Calculation Date in Dax?

Question

I have a Measure Card called 'Apples75th%' and have a Calculated Date in a dropdown slicer.

Certain data gets updated every 7 days so the most recent 7 days selected on the 'Calculation Date' slicer will make the Measure Card show BLANK like the example below.

If the slicer(Calculation Date) is selected for 08/02/2023 then the Apples75th% Measure shows BLANK.
If we go back 7 days from when the last data was updated and I select 01/02/2023 the Value Percentage shows 8.88%

THE DAX FOR THE CARD IS:

Apples75th% = 
IF( 
    SELECTEDVALUE(produce[Product Type]) = "Apples", 
    produce[75apple_percentile],
    produce[75thallproducts_percentile]
)

OLD OUTPUT:

Calculation Date Slicer	Card Apples75%
08/02/2023	BLANK
07/02/2023	BLANK
06/02/2023	BLANK
05/02/2023	BLANK
04/02/2023	BLANK
03/02/2023	BLANK
02/02/2023	BLANK
01/02/2023	8,88%
31/01/2023	6,38%
30/02/2023	9,28%

NEW OUTPUT:

Calculation Date Slicer	Card Value Percentage
08/02/2023	8,88%
07/02/2023	8,88%
06/02/2023	8,88%
05/02/2023	8,88%
04/02/2023	8,88%
03/02/2023	8,88%
02/02/2023	8,88%
01/02/2023	8,88%
31/01/2023	6,38%
30/02/2023	9,28%

My question is, what is the Dax formula to adjust my Measure card so that when the Calculation Date slicer makes it BLANK that it would rather show the more recent Older Calculation Date Value, in this example when I select any Date above the 1st of January it showed blank but I would have liked it to show the 8.88%.

Any help would be appreciated!

Can't help but think that such filling would make it difficult to trust the measure. — Marcus
– Marcus, Commented Feb 8, 2023 at 16:30

Michal · Accepted Answer · 2023-02-09 03:47:34Z

0

Try this:

Create a columns which has the latest date :

 latest date =  CALCULATE(MAX('produce'[Calculation Date Slicer]),
                          FILTER('produce'[Product Type]=EARLIER('produce'[Product Type])))

then create a column which has the last value added:

 latest value = IF('produce'[latest date]='produce'[Calculation Date Slicer],
                    name of column which has value you want to show,
                    BLANK())

then show this result in a card, select a product in a filter and you will see the latest value.

edited Feb 9, 2023 at 3:47

Michal

7,6744 gold badges30 silver badges47 bronze badges

answered Feb 9, 2023 at 2:48

cristobal.huraz

16

Sign up to request clarification or add additional context in comments.

1 Comment

Will Over a year ago

This does not work. Currently there is a column named Calculation Date and a measure(75thapple%) which shows a value. And based on the date selected in the slicer for Calculation date, the most recent dates shows blank. So that is where I get stuck. How to let the measure show not blank but rather a previous date value.

Collectives™ on Stack Overflow

How to show most recent Calculated Value Base on Calculation Date in Dax?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related