Dax Power Pivot Cumulative Total Over Development Period Dimension

Viewed 55

I'm new to Dax and I'm struggling to get the results I require with a running total. I hope I've give you enough information below to help..

thank for any help and pointer in advance.

I'm trying to create an insurance triangle, I have 3 tables fact_transaction_claims_payments, dimension_development_Periods and reporting_upto_information

fact_transaction_claims_payments

dimension_development_Periods

reporting_upto_information

diagram view

I've managed to get my running total to work but only where there is a development period held in the fact table and not all the ones in between held in my development period dimension

Power Pivot Running Total

for example i'd expect development period 1 - 10 to total 0.00 and 13 to 18 to total 103,710 but i can seem to get these to appear in my pivot.

Running Total Dax Expression

RT_TotalAmount1:=VAR CurrentDevelopmentPeriod =
CALCULATE(
    DATEDIFF(
        IF(
            MAX('fact_transaction_claims_payments'[cUWYear]) < 2011
        ,   DATE(MAX('fact_transaction_claims_payments'[cUWYear]),1,1)
        ,   DATE(MAX('fact_transaction_claims_payments'[cUWYear]),4,1)
        )
        ,MAX('fact_transaction_claims_payments'[EndOfMonthDate])

    ,MONTH)
, 'dimension_development_Periods'   ) +1
VAR MaxDevelopmentPeriod = 
CALCULATE(
    DATEDIFF(
        IF(
            MAX('fact_transaction_claims_payments'[cUWYear]) < 2011
        ,   DATE(MAX('fact_transaction_claims_payments'[cUWYear]),1,1)
        ,   DATE(MAX('fact_transaction_claims_payments'[cUWYear]),4,1)
        )
        ,MAX('reporting_upto_information'[EndOfMonthDate])

    ,MONTH)
, 'dimension_development_Periods'   ) +1

VAR RunningTotal =
CALCULATE (
        SUM('fact_transaction_claims_payments'[TotalAmount])
        ,FILTER ( 
                ALL ('fact_transaction_claims_payments')
                ,'fact_transaction_claims_payments'[DevelopmentMonthNumber] <= CurrentDevelopmentPeriod 
            )
    ) 
RETURN
RunningTotal

I've tried adding the ALL('dimension_development_Periods') into the VAR DevelopmentPeriod but this just puts the grand total against every development period.

I'm thinking I now need to use the RunningTotal to calculate against the Development Period Dimension filtered <= the max development period for the cUWYear but I'm not sure on how to implement this and I need some advice can anyone help.

0 Answers
Related