Forum Discussion

Rasmus_Schwerin's avatar
Rasmus_Schwerin
Frequent Visitor
3 years ago

Populating data for previous month when date is missing

Hi everyone,   Been scouring the forums for a solution to my issues but unfortunately haven't been able to figure it out with existing posts, so figured I'd post here and try to get some help on th...
Need Help
Ahmedx's avatar
Ahmedx
Super User
3 years ago

It is important that your model is like this:

and it is enough to write two measures

t1 = 
SUM ( 'Sales'[Quantity] )

t2 = 
VAR _MaxDate =
    MAX ( 'Calendar'[Year Month] )
VAR _MaxDateNoblank =
    CALCULATE (
        LASTNONBLANK ( 'Calendar'[Year Month], [t1] ),
        REMOVEFILTERS ( 'Calendar'[Year Month] )
    )
VAR Results =
    CALCULATE ( [t1], REMOVEFILTERS ( 'Calendar'[Year Month] ) )
RETURN
    IF ( _MaxDate <= _MaxDateNoblank, Results )

Sample PBIX file attached

https://dropmefiles.com/WY9jP


Rasmus_Schwerin's avatar
Rasmus_Schwerin
Frequent Visitor
3 years ago

Hey, 

 

Thank you for your solution, it almost solves my problem. 

 

The issue is that it seems locked to that Month Column, your baseline solution seems to do exactly what I want it to do:

However, since the data isn't in calendar order I tried sorting by another column to make it chronolofical in both my file and in the Sample file you provided. The outcome looks like this:

It looks like ordering the column breaks the measure, any idea on how this could happen?

I have the same data model structure as you proposed above

 

Best,

Rasmus