Populating data for previous month when date is missing

[Rasmus_Schwerin]

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 the issue.

So, I have a dataset which contains information about different values per Product. We can call this the "Product"-table. This table is linked to another table through a one-to-many relationship to a "Generic Info"-table which contains one specific date per product. That table is then in turn linked to a traditional "Calendar"-table. 

Creating a simple measure for sum of the product value and adding it to a Matrix visual looks something like this:

Since the value technically only has one date where it is connected to, this is to be expected. However, due to some logic linked to company forecasting principles they want to have the data show for every single month up to and including the actual month. The end result should therefore look something like this:

I have played around with different filtering contexts, ALL & ALLEXCEPTs but I can't quite get it to work. My gut feeling is that it should be conditional, roughly along these lines:
      IF( [Measure] <> BLANK(), [Measure], [Measure, but modified to show the same value everywhere up to the end date])

I am also thinking I might need to create a second table with all the dates within the measure for this to work, but I have been coming up emptyhanded there as well.

Is this something that anyone could help me with?

Thank you in advance!

Rasmus 

[Ahmedx]

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]

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 

[amitchandak]

@Rasmus_Schwerin , Make sure you have column in date table like

year Month =  year([date])*100 + month([Date])

now have measure, assume already have measure M1

if(isblank([M1]), calculate(lastnonblankvalue(Date[Year Month], [M1]), filter(all(date), Date[Date] <= Max(Date[Date]) ) ) , [M1])

[Rasmus_Schwerin]

I may have spoken too soon @amitchandak 

The measure works perfectly in the rows and calculates everything correctly, however I am getting very unexpected totals when looking at the row and column totals. I tried applying the "Final Word" measures to tackle this problem, but they broke the measure so it doesn't work correctly on the rows anymore.

Example of how the data comes out

If filtered for the individual product it behaves as expected, but as soon as I add in more it starts weirding out. It looks like it only considers the product with the earliest "End Date" in the data and calculates the total based on that and ignores any other product. Feels like it should be possible to add something to the filter statement to make it calculate properly but whatever I do seems to break it further. Any ideas?

My Measure: (the "period parameter" is a simple what-if parameter with a value range)

Claims Annualized = 

SUMX(
    HTA_Data, 
    HTA_Data[Claims] * 
    CALCULATE(
        IF(
            FIRSTNONBLANK('Annualization Factors - Unpivoted'[Value], 1) = 0, 1, 
            FIRSTNONBLANK('Annualization Factors - Unpivoted'[Value], 1)
            
        )
    )
)


Claims Daily = 

[Claims Annualized] / 'Period Parameter'


Claims Over Time = 

var _Seasonality = 
    1 + SELECTEDVALUE(Seasonality[Seasonality Claims])

var _Inflation = 
    1 + SELECTEDVALUE(Inflation[Inflation Claims])

var _FirstReportingDate = 
    min('Calendar'[Date])
var _LastReportingDate = 
    max('Calendar'[Date])

var _days_in_period =  
    CALCULATE(
                COUNT(calendar[date]),
                ALL('Calendar'[Date]),
                Calendar[date] <= _LastReportingDate,
                Calendar[date] >= _FirstReportingDate)

var _calculation = 
    IF(
        isblank([Claims Daily]), 
            CALCULATE(lastnonblankvalue('Calendar'[Year Month], 
            [Claims Daily]), 
            filter(all('Calendar'), 'Calendar'[Date] >= Max('Calendar'[Date]) ) 
            ) 
            , [Claims Daily])



return 
_calculation * _days_in_period * _Seasonality * _Inflation

[Rasmus_Schwerin]

Thank you!

This measure works, I needed to flip the conditional statement to ">=" for it to work, otherwise it was doing the opposite of what I was intending.

Really appreciate the assistance, thank you

/Rasmus