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 f...
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




    Fill values for missing dates using previous available value.pbix48 KB
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




Fill values for missing dates using previous available value.pbix48 KB
  • 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?