Create measure to return text from selected date & selected date -1

[Anonymous]

Hi, looking to compare grades between selected date from filter and selected date -1.

Eg of table contents

Company   Date              Grade

ABC            19-01-2020   Red

ABC            20-01-2020   Amber

XYZ            19-01-2020   Green

XYZ            20-01-2020   Amber 

XYZ            21-01-2020   Red

If I filtered on Date = 20-01-2020, the desired output in a Table visualisation should look like this

Company  Date             Grade    Prev_date       Prev_date_Grade

ABC           20-01-2020  Amber   19-01-2020   Red

XYZ           20-01-2020  Amber   19-01-2020    Green

Can one help?

[v-yingjl]

Hi @Anonymous ,

You can create these two measures:

Prev_date = SELECTEDVALUE('Table'[Date]) -1

Prev_Date_Grade =
CALCULATE (
    MAX ( 'Table'[Grade] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date]
            = SELECTEDVALUE ( 'Table'[Date] ) - 1
            && 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] )
    )
)

You will get your hoped result like this:

Here is my sample file that hopes to help you, please check and try it: Create measure to return text from selected date.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, just realised I need to adapt this to exclude non working days so if selected date was a Monday then date -1 should refer to previous Friday, bonus if we can also exclude UK bank holidays.

table

Company   Date              Grade

ABC            17-01-2020   Red

ABC            20-01-2020   Amber

XYZ            17-01-2020   Green

XYZ            20-01-2020   Amber 

XYZ            21-01-2020   Red

If selected date = 20-01-2020 then output to look like below

Company  Date             Grade    Prev_date       Prev_date_Grade

ABC           20-01-2020  Amber   17-01-2020   Red

XYZ           20-01-2020  Amber   17-01-2020    Green

Can you help?

[v-yingjl]

Hi @Anonymous ,

You can modified the two measures what I have first posted like this:

Prev_date = 
VAR _pre =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( WEEKDAY ( _pre, 2 ) = 1, _pre - 3, _pre - 1 )

Prev_Date_Grade = 
CALCULATE (
    MAX ( 'Table'[Grade] ),
    FILTER (
        ALL ( 'Table' ),
        (
            'Table'[Date]
                = SELECTEDVALUE ( 'Table'[Date] ) - 3
                || 'Table'[Date]
                    = SELECTEDVALUE ( 'Table'[Date] ) - 1
        )
            && 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] )
    )
)

You will get your expected result:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Almost there. I'm testing this on my table with 000's of records. It seems if company XYZ has entry on Tuesday, it refers back to previous Friday (date-3) than Monday (date-1). I think the OR statement below needs refining.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

This is my sample table:

Based on my test, 2020/1/21 is Tuesday, it will return the right result using previous formula:

My sample file is attached: Create measure to return text from selected date.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try something like

Noncontinous Date

Last Grade = CALCULATE(Max('Table'[Grade]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))

continuous Date
Last Grade = CALCULATE(Max('Table'[Grade]),dateadd(Date[Date],-1,Day))

Prefer to use date table in place table[Date],

like

Last Grade = CALCULATE(Max('Table'[Grade]),filter(all(Date),Date[Date] =MAXX(FILTER(all(Date),Date[Date]<max(Date[Date])),Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[parry2k]

@Anonymous try this measure, As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Prev Date Value = 
CALCULATE ( MAX ( Table[Grade] ), ALLEXCEPT ( Table, Table[Company] ), 
DATEADD( DateTable[Date], -1, DAY ) 
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!