Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
waitehamrick
Helper I
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. 

 

waitehamrick_0-1677882612774.png

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
FreemanZ
Super User
Super User

hi @waitehamrick 

 

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:

FreemanZ_0-1677901769487.png

 

View solution in original post

hi @waitehamrick 

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

View solution in original post

3 REPLIES 3
waitehamrick
Helper I
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. 

hi @waitehamrick 

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
Super User
Super User

hi @waitehamrick 

 

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:

FreemanZ_0-1677901769487.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.