How to modify DAX expression to fill up blanks in running total Power BI

Question

The goal is to fill up blanks with previous values.

Thankful to @Alexis Olson I was able to achieve this for development year. Post can be found here:

How to fill out blanks in Running Total matrix Power BI

But now I need the same for development month and this is where I stuck.

What I've done:

--created calculated table 'Dev Months' that retrieves all development months. --tried to modify expression by manipulating Dem Month

     Accident Month Losses1 = 
VAR CurrDevMonth = MAX('Dev Months'[DevMonth])
VAR MaxMonth = CALCULATE(MAX(fact_Losses[AccidentMonth]), ALLSELECTED(fact_Losses))
VAR CurentMonth = MAX(fact_Losses[AccidentMonth])
VAR CurentYear = MAX(fact_Losses[AccidentYear])
RETURN


IF(
    MONTH(
        EOMONTH(
            DATE(CurentYear - 1,12,31),
            CurrDevMonth)
     ) > MaxMonth,
    BLANK(),
    CALCULATE( 
        SUM(fact_Losses[PaymentAmount]),
        FILTER(ALL(fact_Losses[DevMonth]), fact_Losses[DevMonth] <= CurrDevMonth)
    )
)

--Tried to use AccidentDate and break it down by Year and Month --Tried to convert Year-Month to DATE Still no success

Data looks like this:

When I use matrix to display data I have blanks.

Columns: Month-Year,

Rows: Development months. Can be more than 12 since its developing over time.

Values: Running total

If there is a blank (no loss) then it should be populated with previous number, based on development month.

Running Total Dax expression :

Accident Month Losses = 

    CALCULATE( 
    [Total Loss],
    FILTER(ALL(fact_Losses[DevMonth]),fact_Losses[DevMonth] <= MAX(fact_Losses[DevMonth])
    ))

Need to be like this:

.pbix can be found here: https://www.dropbox.com/s/zujp6p9lbyqfez7/Test_LossTriangle.pbix?dl=0

Please help

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-26T16:54:06

So the issue here is that MAX(fact_Losses[DevMonth]) is null for those missing cells because there are no corresponding transactions.

You can fix my removing filter context imposed by dim_Date.

Accident Month Losses =
VAR CurrDevMonth =
    CALCULATE ( MAX ( fact_Losses[DevMonth] ), ALL ( dim_Date ) )
VAR RunningLoss =
    CALCULATE (
        [Total Loss],
        FILTER ( ALL ( fact_Losses[DevMonth] ), fact_Losses[DevMonth] <= CurrDevMonth )
    )
RETURN
    RunningLoss

All I've done so far is put the MAX inside a CALCULATE so that I can remove the dim_Date filter context and put things in terms of variables to more easily understand what's going on.

The issue now is that the whole table is full instead of being upper triangular. To fix this, we can tell it to return a blank if the current month rolled forward by DevMonth number of months is greater than the maximal month.

Accident Month Losses =
VAR CurrDevMonth =
    CALCULATE ( MAX ( fact_Losses[DevMonth] ), ALL ( dim_Date ) )
VAR RunningLoss =
    CALCULATE (
        [Total Loss],
        FILTER ( ALL ( fact_Losses[DevMonth] ), fact_Losses[DevMonth] <= CurrDevMonth )
    )
VAR MaxYearMonth =
    CALCULATE (
        EOMONTH ( MAX ( fact_Losses[AccidentDate] ), 0 ),
        ALLSELECTED ( fact_Losses )
    )
VAR CurrYearMonth = MAX ( dim_Date[Date] )

RETURN
    IF (
        EOMONTH ( CurrYearMonth, CurrDevMonth - 1 ) > MaxYearMonth,
        BLANK (),
        RunningLoss
    )

This will also remove some of the subtotals, but you can get those back if you add a couple of conditions to your IF conditional.

    IF (
        EOMONTH ( CurrYearMonth, CurrDevMonth - 1 ) > MaxYearMonth
            && ISFILTERED( dim_Date[Year Month] )
            && ISFILTERED( fact_Losses[DevMonth] ),
        BLANK (),
        RunningLoss
    )

How to modify DAX expression to fill up blanks in running total Power BI

2 Answers