Forum Discussion

PawelHalamoda's avatar
PawelHalamoda
Frequent Visitor
2 years ago
Solved

Fill values for missing dates using previous available value

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/399958#M182659

 

 

 

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.







Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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




2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      PawelHalamoda
      Frequent Visitor
      2 years ago

      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?