Forum Discussion

wlknsn's avatar
wlknsn
Icon for Helper I rankHelper I
8 years ago

Fill empty rows/values with previous/last measure or calculated value

Hi,   I'm trying to find a DAX solution for a measure or calculated column which basically returns the last measure/calculated value if the cell is empty. This result should not be an aggregation, ...
Need Help
wlknsn's avatar
wlknsn
Icon for Helper I rankHelper I
8 years ago

As mentionned before, I need to fill this based on a measure. The power query solution is based on data, and not a measure/calculation. Same for the dax solution, that will not work with measure. It's the last measure that needs to be filled.

Yggdrasill's avatar
Yggdrasill
Icon for Responsive Resident rankResponsive Resident
7 years ago

I've used this as a calculated column

 

Fill empty rows = 
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( dimDates[Date]; 1 );
        FILTER (
            ALL ( dimDates );
            dimDates[Date]<= EARLIER (  dimDates[Date] )
                && NOT ( ISBLANK ( dimDates[YourColumnWithBlankRows]) )
        )
    )
RETURN
    CALCULATE (
        SUM ( dimDates[YourColumnWithBlankRows] );
        FILTER ( ALL ( dimDates ); dimDates[Date] = LastNonBlankDate )
    )