Need a Dax for Last Date Change

[Anonymous]

Hi, 

I am trying to write an DAX that will give me the last date a rate changed. If you look at the below data the the unit has had two changes over the span of two years. I would like it to give me only the most recent date teh rate changed which is 12/1/2022. 

This is the dax I wrote but it is giving me 1/1/2023 for all dates after 12/1/2023. Can someone help? 

        CALCULATE(

            MAX(table[ Date]),

            FILTER(

                ALL(table),

                table[unit]= EARLIER(table[unit])

                && table[rate] <> CALCULATE(LASTNONBLANK(table[rate],0), FILTER(ALL(table), table[Unit]= EARLIER(table[unit])))

        ))

[FreemanZ · Accepted solution]

hi @Anonymous 

try to plot a card visual with a measure like:

Measure = 
VAR _table =
ADDCOLUMNS(
    TableName,
    "Flag",
    VAR _rate = [Rate]
    VAR _date = [Date]
    VAR _ratepre = 
    MAXX(
        FILTER(ALL(TableName), TableName[Date] = EDATE(_date, -1) ),
        TableName[Rate]
    )
    RETURN
    IF( _rate<>_ratepre, "Yes", "No")
)
RETURN
MAXX(
    FILTER(
        _table,
        [Flag]="Yes"
    ),
    TableName[Date]
)

it worked like:

[Anonymous]

Thanks for the response! I need this measure for a report I am trying to build. I dont think this formula is taking into account that there are multiple different unit number is my data set so it is only returning the max date in the file. 

[FreemanZ · Accepted solution]

hi @Anonymous 

then remove ALL, like:

Measure = 

VAR _table =

ADDCOLUMNS(

    TableName,

    "Flag",

    VAR _rate = [Rate]

    VAR _date = [Date]

    VAR _ratepre = 

    MAXX(

        FILTER(TableName, TableName[Date] = EDATE(_date, -1) ),

        TableName[Rate]

    )

    RETURN

    IF( _rate<>_ratepre, "Yes", "No")

)

RETURN

MAXX(

    FILTER(

        _table,

        [Flag]="Yes"

    ),

    TableName[Date]

)

[FreemanZ · Accepted solution]

hi @Anonymous 

try to plot a card visual with a measure like:

Measure = 
VAR _table =
ADDCOLUMNS(
    TableName,
    "Flag",
    VAR _rate = [Rate]
    VAR _date = [Date]
    VAR _ratepre = 
    MAXX(
        FILTER(ALL(TableName), TableName[Date] = EDATE(_date, -1) ),
        TableName[Rate]
    )
    RETURN
    IF( _rate<>_ratepre, "Yes", "No")
)
RETURN
MAXX(
    FILTER(
        _table,
        [Flag]="Yes"
    ),
    TableName[Date]
)

it worked like: