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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Fill empty rows/values with previous/last measure

Hi All,

 

I am really at a loss on this one. My dataset has a reciept date column, I also have a Calendar table to bring in all dates within a month. Bascially what I want to do is make sure there are values for all dates within the year (not just the reciept dates). If there is no value for a specific row, I want it to bring in the last date value. 

 

I tried to do the below and it is ALMOST what I want but not quite. 

 

LastNonBlank Value =
var LastNonBlankDate=CALCULATE(MAX('Calender'[Date]),FILTER(ALL('Calender'),Calender[Date]<=MAX('Calender'[Date]) && 'Master PPV Table'[Average of Actual]<>0))
return
CALCULATE([Average of Actual],
    FILTER(ALL('Calender'),Calender[Date]=LastNonBlankDate))
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

Assume that the field [Average of Actual] is a fact field not measure, you can create a measure as below. Then create a visual with this setting( X axis: [Date] of table Calender   Value: New created measure [Average Actual values]).

Average Actual values =
VAR Curdate =
    MAX ( 'Calender'[Date] )
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'Master PPV Table'[Date] ),
        FILTER (
            ALL ( 'Master PPV Table' ),
            'Master PPV Table'[Date] < Curdate
                && NOT ( ISBLANK ( 'Master PPV Table'[Average of Actual] ) )
        )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Master PPV Table'[Average of Actual] ) ),
        CALCULATE (
            [Average of Actual],
            FILTER (
                ALL ( 'Master PPV Table' ),
                'Master PPV Table'[Date] = LastNonBlankDate
            )
        ),
        SUM ( 'Master PPV Table'[Average of Actual] )
    )

If the above one can't get the correct result, please provide some sample data and the formula of measure if it involve any measure.

Best Regards

Rena

 

 

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

Assume that the field [Average of Actual] is a fact field not measure, you can create a measure as below. Then create a visual with this setting( X axis: [Date] of table Calender   Value: New created measure [Average Actual values]).

Average Actual values =
VAR Curdate =
    MAX ( 'Calender'[Date] )
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'Master PPV Table'[Date] ),
        FILTER (
            ALL ( 'Master PPV Table' ),
            'Master PPV Table'[Date] < Curdate
                && NOT ( ISBLANK ( 'Master PPV Table'[Average of Actual] ) )
        )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Master PPV Table'[Average of Actual] ) ),
        CALCULATE (
            [Average of Actual],
            FILTER (
                ALL ( 'Master PPV Table' ),
                'Master PPV Table'[Date] = LastNonBlankDate
            )
        ),
        SUM ( 'Master PPV Table'[Average of Actual] )
    )

If the above one can't get the correct result, please provide some sample data and the formula of measure if it involve any measure.

Best Regards

Rena

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

You seem to be reinventing the LASTNONBLANKVALUE() function.

 

Technically you can do the same in Power Query via Fill..Up.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.