in time cumulative total using SCD history table

[Anonymous]

i have a SCD type 2 (History) table which has claimhistoryid unique key, this key is connected to fact table using 1 to many relationship. fact is also connected with date dimension as shown in the image below. i need to calculate two measures, one to sum the base transaction amount, which works fine. I need to calculate cumulative total value based on historical records. Also performance is critical because fact and history table are large tables and we use azure as tabular models. see example below, can you help?

[tamerj1]

Hi @Anonymous 

you may try

Cumulative Total =
VAR CurrentID =
    MAX ( claimHistory[Claim History ID] )
RETURN
    CALCULATE (
        [Sum base transaction Amount],
        ALLEXCEPT ( claimHistory, claimHistory[ClaimNbr] ),
        claimHistory[Claim History ID] <= CurrentID
    )

[Anonymous]

this doesn't work either, this also shows the same result as sum of base transaction amt, it doesn't total at the claim level, just provide total at history level, also poor with performance.

[tamerj1]

@Anonymous 

I guess you are right. It won't work all at one step. Please try

Cumulative Total =
VAR CurrentID =
    MAX ( claimHistory[Claim History ID] )
VAR CurrentClaimTable =
    CALCULATETABLE (
        claimHistory,
        ALLEXCEPT ( claimHistory, claimHistory[ClaimNbr] )
    )
VAR PreviousIDsTable =
    FILTER ( CurrentClaimTable, claimHistory[Claim History ID] <= CurrentID )
RETURN
    CALCULATE ( [Sum base transaction Amount], PreviousIDsTable )

[amitchandak]

@Anonymous , what is cumulative formula

calculate([Sum base transaction Amount]), Filter(allselected('Date') , 'Date'[Date] <= max('Date'[Date])) , filter(allselected('claimHistory'), 'claimHistory'[claimHistoryID] =max('claimHistory'[claimHistoryID]) ) )

[Anonymous]

this will not work, this gives me the same thing (in my first view) what i do not want. basically i want cumulative total on claim level not on it's history level. we have claimkey in the fact table also but it is not being used to make a relationship, relationship is based on history key which has all current and historical records of the claim.