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
amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
    Rasmus_Schwerin
    Frequent Visitor
    3 years ago

    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

  • Rasmus_Schwerin's avatar
    Rasmus_Schwerin
    Frequent Visitor
    3 years ago

    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