time series Fill in blanks previous value <> blank

[Chris_23]

Hello everybody,

i already have posted the problem and i have recieved a solution (Gap filler - previous value  thx @v-kkf-msf)

But i have to notice, that i didn't explained my problem clear enough. So that the solution did not work for me.

I need a measure for filling the gaps if there are previous values <> blank().

I already have tried the solution/measure from Fill in blanks with last non blank value (using DAX measure) 

But it did't work here because for example A015690 hasn't a row for Nov 2021.

So the measure should identify, if the value is blank then take the next value date < Nov 21.

Example.pbix 

Any ideas how to fix the problem?

Thanks a lot

Chris

[Anonymous · Accepted solution]

Hi @Chris_23 ,

I have no access to your shared file link (Example.pbix ), so I just created a sample pbix file(see attachment) for you base on your provided screenshot. Please check whether that is what you want. You can create two measures as below to get the values...

Measure = 
VAR _curdate =
    SELECTEDVALUE ( 'SortOrder'[Date] )
VAR _curitem =
    SELECTEDVALUE ( 'Table'[Item No_] )
VAR _value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Date] = _curdate )
    )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Item No_] = _curitem
                && 'Table'[Date] < _curdate
                && SUM ( 'Table'[Value] ) <> BLANK ()
        )
    )
VAR _prevalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Item No_] = _curitem
                && 'Table'[Date] = _predate
        )
    )
RETURN
    IF ( ISBLANK ( _value ), _prevalue, _value )

FillBlank = SUMX ( VALUES ( 'SortOrder'[OrdDate] ), [Measure] )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[Chris_23]

brilliant. So much work. Thank you very much.