Fill values for missing dates using previous available value

[PawelHalamoda]

Hi - I am trying to fill gaps in data to have continous dates in table with according values (previous value for this model).

My model:

Currently when I build table visual it looks like following (important is that FD table has mixed InStock for different models), one model is filtered using slicer:

So expected outcome is to have continous dates, if date does not have value it should take previous one for selected model.
Example between 21/07/2022 and 25/07/2023 InStock should be 96, then from 26/07/2022 it should turn into 0.

I have tried measure from this topic: https://community.fabric.microsoft.com/t5/Desktop/How-to-fill-the-gaps-in-values-between-dates/m-p/3...

MEASURE =
VAR data =
    SELECTEDVALUE ( Kalendarz[Data] )
RETURN
    CALCULATE (
        LASTNONBLANK ( 'FINE DINE'[InStock], 1 ),
        'FINE DINE'[Data] <= data,
        CROSSFILTER ( Kalendarz[Data], 'FINE DINE'[Data], NONE )
    )

I am really close but outcome is as following:

Any idea why it happens with given measure?

Thanks for any ideas and clues.

[v-heq-msft · Accepted solution]

Hi @PawelHalamoda ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a calculate column

Previous Non Zero Stock = 
CALCULATE(
    LASTNONBLANK('FINE DINE'[InStock], 1),
    FILTER(
        ALL('FINE DINE'),
        'FINE DINE'[Model] = EARLIER('FINE DINE'[Model]) &&
        'FINE DINE'[Date] < EARLIER('FINE DINE'[Date])
    )
)

Create a measure

Adjusted Stock = 
IF(
    SELECTEDVALUE('FINE DINE'[InStock]) = 0,
    SELECTEDVALUE('FINE DINE'[Previous Non Zero Stock]),
    SELECTEDVALUE('FINE DINE'[InStock])
)

Final output

Best regards,

Albert He

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

[PawelHalamoda]

Hi, thanks for all your efforts.

Unfortunately not really suitable for my requirement, first of all "Previous Non Zero Stock" measure does not work as DAX for some reason does not accept 'FINE DINE'[Model] as argument.

Secondly outcome you have presented is not right:

Please note that Adjusted Stock should produce 96 for 22/07/2023 - 25/07/2023 (those dates are missing). Then from 26/07/2023 it should turn to 0 (keep it as it is also vital information) and keep it zero for all continous dates till 15/10/2023.

Any ideas how to adjust it?