Cumulative Sum of 2 measures based on seperate age calculations

[martinrowe]

I'm relatively new to PBI, but I have faily solid understand of how it works but I'm struggling to get this to work and cant seem to find anything out there than could help me solve my problem.

I have 2 tables, which have a 1:n relationship between them. In their respective tables I have calculated columns with what I will refer to as an "age calculation" which gives me a duration in days between 2 dates which represent timestamps of when the relative events occur.

Table 1 the key events are: Application_Date & Paid_Date which i use for the age/duration calculation

Table 2 the key events are: Paid_Date & Fraud_Date which I use to calculate age/duration

The events in each table also have a Transaction_Amount which I am able to calculate a measure that gives me the sum of the transactions by the respective durations. I am also able to calculate the cumulative SUM for these amounts over these same durations

My problem starts when I try to plot these on the same graph (line and clusters column chart). Depending on which age/duration I use for the shared axis, the two measures based on the other duration doesnt calculate the cumulative amount properly.

I assume that this is because the two age/duration calculations are seperate but I am unable to see a way where I can plot them against a "common duration" on the axis. I thought I might beable to create a seperate table for these durations and set up a relationship but that doesnt work as I would need to remove the current 1:n relationship which is used for many other things.

The green columns and red line here are fine. The black columns should be offset to the right, and normally occur from day 10 onwards and the yellow line should show that cumulative value.

I have pasted both cumulative total expressions below.

Total Revenue Running Total = 
CALCULATE (
    SUM(Transaction_History[Transaction_Amount]),
    FILTER (ALLSELECTED(Transaction_History), Transaction_History[Days Between App & Transaction] <= MAX (Transaction_History[Days Between App & Transaction])),Transaction_History[Transaction_Type]="sale",Transaction_History[Response_Code]="0"
)

Total CB Value Running Total = 
CALCULATE (
    SUM(Fraud_History[Transaction_Amount]),
    FILTER (ALLSELECTED(Fraud_History), Fraud_History[Days Between Transaction & Fraud] <= MAX (Fraud_History[Days Between Transaction & Fraud])), Fraud_History[Fraud_Type] = "chargeback"
)

If I can solve this problem my next step would be to add an addition measure which shows the NET value between these 2 cumulative measures.

My overall aim to be able to represent a 'lifetime value' for certain subsets of customer who are deemed "High-Risk"

Any help, guidance is appreciated.

[v-ljerr-msft · Accepted solution]

Hi @martinrowe,

My problem starts when I try to plot these on the same graph (line and clusters column chart). Depending on which age/duration I use for the shared axis, the two measures based on the other duration doesnt calculate the cumulative amount properly.

---

Suggestion 1: Rewrite one of the two measures based on the other duration. For example, if you use the duration column from "Transaction_History" table as shared axis, then you can rewrite the "Total CB Value Running Total" measure like below.

Total CB Value Running Total =
VAR currentDuration =
    MAX ( Transaction_History[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Fraud_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Fraud_History ),
            Fraud_History[Days Between Transaction & Fraud] <= currentDuration
        ),
        Fraud_History[Fraud_Type] = "chargeback"
    )

I thought I might beable to create a seperate table for these durations and set up a relationship but that doesnt work as I would need to remove the current 1:n relationship which is used for many other things.

---

Another suggestion: Create a separate table for these durations and do not set up any relationships with the other two tables. Then rewrite the two formulas like below, and last use the duration column from the new created table as shared axis on the chart.

Total Revenue Running Total =
VAR currentDuration =
    MAX ( NewTable[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Transaction_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Transaction_History ),
            Transaction_History[Days Between App & Transaction] <= currentDuration
        ),
        Transaction_History[Transaction_Type] = "sale",
        Transaction_History[Response_Code] = "0"
    )

Total CB Value Running Total =
VAR currentDuration =
    MAX ( NewTable[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Fraud_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Fraud_History ),
            Fraud_History[Days Between Transaction & Fraud] <= currentDuration
        ),
        Fraud_History[Fraud_Type] = "chargeback"
    )

Regards

[martinrowe]

Thank for the great feedback. I went with your first suggestion, but in doing so created a second version of the measure as the original one was useful the way it was in other areas.

Though a the first suggestion does get me to my end point now, It does feel a little bit like a fudge in the context of the wider data model I have, so i'm going to continue to look at your 2nd suggestion to see if there is a way I can make this work for this and other similar calculations/visulations I have

Thanks again - Martin