Need help with DAX to return the previous date's result

[Tommy_K78]

Dear community, 

I’m having trouble aligning the previous data with the current data on a specific date in a matrix table.

I have a matrix table displaying the Total for specific dates each month, and I want to add a second row to show the Total Prior, similar to the example below. 

Application Date and Settlement Date are on two different date dimension.

The issue I’m facing is that the Total Prior is appearing on the next day, even though there are no Total sales.

Total Prior = CALCULATE ( [Total], PREVIOUSDAY( ( dim_settlement_date[settlement_dt] ) ))

Thanks for your help.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

ROWNUMBER function (DAX) - DAX | Microsoft Learn

LastNonBlank amount: =
VAR _t =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                Data,
                DimApplicationDate[Year-Month],
                DimApplicationDate[Year-Month sort],
                DimSettlementDate[Date]
            ),
            REMOVEFILTERS ()
        ),
        "@total", [Total:]
    )
VAR _rownumber =
    ADDCOLUMNS (
        _t,
        "@rownumber",
            ROWNUMBER (
                _t,
                ORDERBY (
                    DimApplicationDate[Year-Month sort], ASC,
                    DimSettlementDate[Date], ASC
                )
            )
    )
VAR _currentrownumber =
    MAXX (
        FILTER (
            _rownumber,
            DimApplicationDate[Year-Month sort]
                = MAX ( DimApplicationDate[Year-Month sort] )
                && DimSettlementDate[Date] = MAX ( DimSettlementDate[Date] )
        ),
        [@rownumber]
    )
VAR _prevrownumber = _currentrownumber - 1
RETURN
    MAXX ( FILTER ( _rownumber, [@rownumber] = _prevrownumber ), [@total] )

[Tommy_K78]

This is amazing and thank you so much. However, when I add slicers, part of the results appears to change.

This is the pbix file I'm using Tommy_K78_pbix

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and I thought only two dim tables were there, and I just add the condition in the measure REMOVEFILTERS()

If there are more dimension tables, I need to specify which dimension tables to apply REMOVEFILTERS() function in the measure.

Please check the below fixed-measure and the attached pbix file whether it suits your requirement.

LastNonBlank amount: = 
VAR _t =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                Data,
                DimApplicationDate[Year-Month],
                DimApplicationDate[Year-Month sort],
                DimSettlementDate[Date]
            ),
            REMOVEFILTERS (DimApplicationDate), REMOVEFILTERS(DimSettlementDate)
        ),
        "@total", [Total:]
    )
VAR _rownumber =
    ADDCOLUMNS (
        _t,
        "@rownumber",
            ROWNUMBER (
                _t,
                ORDERBY (
                    DimApplicationDate[Year-Month sort], ASC,
                    DimSettlementDate[Date], ASC
                )
            )
    )
VAR _currentrownumber =
    MAXX (
        FILTER (
            _rownumber,
            DimApplicationDate[Year-Month sort]
                = MAX ( DimApplicationDate[Year-Month sort] )
                && DimSettlementDate[Date] = MAX ( DimSettlementDate[Date] )
        ),
        [@rownumber]
    )
VAR _prevrownumber = _currentrownumber - 1
RETURN
    MAXX ( FILTER ( _rownumber, [@rownumber] = _prevrownumber ), [@total] )

[Tommy_K78]

Thanks, this worked like a charm!