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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PawelHalamoda
Frequent Visitor

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:

PawelHalamoda_0-1711631354607.png


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:

PawelHalamoda_2-1711631990523.png

 

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:

PawelHalamoda_3-1711632081273.png

Any idea why it happens with given measure?


Thanks for any ideas and clues.







1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1711693381481.png

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])
    )
)

vheqmsft_1-1711693468632.png

Create a measure

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

Final output

vheqmsft_2-1711693514470.png

vheqmsft_3-1711693525945.png

 

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




View solution in original post

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1711693381481.png

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])
    )
)

vheqmsft_1-1711693468632.png

Create a measure

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

Final output

vheqmsft_2-1711693514470.png

vheqmsft_3-1711693525945.png

 

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




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:

PawelHalamoda_0-1712228855565.png

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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