Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Can't get DAX to Find Values Filtering by Date

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 dec...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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/2022cat b   1
07/02/2022cat c   6
06/02/2022cat a   7
06/02/2022cat b   3
06/02/2022cat 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's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

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