topic Re: Need a Dax for Last Date Change in DAX Commands and Tips

Need a Dax for Last Date Change

Anonymous — Fri, 03 Mar 2023 22:33:11 GMT

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])))

))

Re: Need a Dax for Last Date Change

FreemanZ — Sat, 04 Mar 2023 03:50:41 GMT

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:

Re: Need a Dax for Last Date Change

Anonymous — Sat, 04 Mar 2023 17:25:58 GMT

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.

Re: Need a Dax for Last Date Change

FreemanZ — Mon, 06 Mar 2023 00:27:23 GMT

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]

)