Load previous data even when no rows exist

[Anonymous]

Hi @smparnon ,

Based on the description, try to use LOOKUPVALUE DAX to modify the formula to the following.

RASI GDMT Adjusted = 
VAR SelectedEndDate = MAX(Overall[Flowsheet Recorded Date])
VAR FirstRecordDate = MIN(Overall[MostRecentACE/ARB/ARNIDate])
VAR LastAvailableDate = CALCULATE(MAX(Overall[MostRecentACE/ARB/ARNIDate]), ALL(Overall))
VAR AdjustedEndDate = IF(SelectedEndDate > LastAvailableDate, LastAvailableDate, SelectedEndDate)
VAR CountTillLastAvailable = 
    IF(
        SelectedEndDate < FirstRecordDate,
        BLANK(),
        CALCULATE(
            COUNTROWS(
                SUMMARIZE(
                    FILTER(
                        Overall,
                        (Overall[MostRecentACE/ARB/ARNIValue] = "max tolerated" || Overall[MostRecentACE/ARB/ARNIValue] = "Target dose") &&
                        Overall[MostRecentACE/ARB/ARNIDate] <= AdjustedEndDate
                    ),
                    Overall[patient name and epic id]
                )
            ),
            ALLSELECTED(Overall)
        )
    )
RETURN
IF(ISBLANK(CountTillLastAvailable), LOOKUPVALUE([column name], [Flowsheet Recorded Date], LASTDATE(Overall[Flowsheet Recorded Date])), CountTillLastAvailable)

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

LASTDATE function (DAX) - DAX | Microsoft Learn

Solved: Previous Value Using DAX - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins