Can't get DAX to Find Values Filtering by Date

[Anonymous]

Hi everyone
A relatively straightforward problem I'm having here:

I have a table called Market Moves which is set up a little like this and is filtered in Power Query by the PRICEDATE column in decending order:

PRICEDATE	PRICE
15/10/2021	8
13/10/2021	5
12/10/2021	9
11/10/2021	3

I have this DAX measure set up which provides me with the date of the second most recent date available in the PRICEDATE column from the table above:

second-to-last activity date = 
CALCULATE (MAX('Market Moves'[PRICEDATE] ), FILTER('Market Moves',[PRICEDATE]  <> MAX( ( 'Market Moves'[PRICEDATE] ))))

 
That works fine. I can use this measure to reference the date to filter in PRICEDATE...but how do I do that?
What I want to achive is:
1. Find the most recent price value by date as listed in PRICEDATE (a)

2. Find the second most recent price value by date as listed in PRICEDATE (b)
3. Find the last date of last year (could be the 31st but sometimes might not be) as listed in PRICEDATE (c)
4. Price difference (b) - (a)
5. Price difference (c) - (a)

i am not sure if it is best to use a LOOKUPVALUE or FILTER. Any help really apprecaited.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following measures:

LastDate = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] >= MAX ( 'Table'[Date] ) )
)

most recent price = 
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] = [LastDate] )
)

second most recent price = 
VAR seconddate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] <> [LastDate] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Price] ),
        FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] = seconddate )
    )

last date of last year = 
CALCULATE (
    SUM ( 'Table'[Price] ),
    ALL ( 'Table' ),
    LASTDATE ( ALLSELECTED ( 'Table'[Date] ) )
)

b- a = [second most recent price] - [most recent price]

c - a = [last date of last year] - [most recent price]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-kkf-msft Many many thanks for your helpful responses and measures. They all work.

[Anonymous]

Hi @lbendlin 
Many thanks for this and the attachment. Yes i did mean sorted rather than filtered.
In fact, the dates are not unique. You might get this for example:

Date	Name	Price
07/02/2022	cat a	23
07/02/2022	cat b	1
07/02/2022	cat c	6
06/02/2022	cat a	7
06/02/2022	cat b	3
06/02/2022	cat c	8
05/02/2022	cat a	5
05/02/2022	cat b	3
05/02/2022	cat c	5
04/02/2022	cat a	7
04/02/2022	cat b	4

This being the case, would anything change to the approach you mentioned? I looked in your pbix file andm couldn't see in the penultimate price column that it references the price of the previous day for that category name. I think I would maybe need to apply a second filter to the CALCULATE to filter by the category name? Is that right? How would that be expressed?

[lbendlin]

It would be better if your data has an index column to make each row unique.

[lbendlin]

" is filtered in Power Query by the PRICEDATE column in decending order"

That's not a thing. Did you mean "sorted" ?  Generally sorting in Power Query is not required. It would be better if your data had an index column.  For now we would need to assume that the dates are unique?

If you want to compute your logic over the whole table you don't need a measure - a calculated column will be sufficient. Measures are only required if the result can be impacted by user interaction.  Making another assumption here that the measure needs to be calculated for each line in the visual.

Attached is a raw draft. As you can see all rows return the same data - an indication that a column would have been sufficient.  But you can also apply filters (on the date, for example) and see that the value changes.