Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
))
Solved! Go to Solution.
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:
then remove ALL, like:
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.
then remove ALL, like:
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |