cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Need a Dax for Last Date Change

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

))

2 ACCEPTED SOLUTIONS
Super User

try to plot a card visual with a measure like:

Measure =
VAR _table =
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:

Super User

then remove ALL, like:

Measure =
VAR _table =
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]
)
3 REPLIES 3
Helper I

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.

Super User

then remove ALL, like:

Measure =
VAR _table =
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]
)
Super User

try to plot a card visual with a measure like:

Measure =
VAR _table =
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: