Dynamic line chart for CUSUM analysis

[BenFransen]

I'm wondering how I can make a dynamic line chart. The goal I'm trying to achieve is to create a CUSUM chart. In hospital care this is used to plot a trend line on how you're doing regarding mortality versus the calculated risk a patient could die during surgery. The formula is very easy: starting at 0, for each event add the the risk score (percentage) a patient could die if the patient didn't die during hospital admission or substract that same value if the patient did die.

For example this could be the data (where CUSUMValue needs to be calculated on the fly)

EventID  RiskScore, PatientDied  CUSUMValue

1, 8, False, 8

2, 4, False, 12

3, 5, False, 17

4, 29, True, -12

5, 9.4, False, -2.6

In my case of course I have a date column as well and the dataset contains data of the last 12 months including all data from the current month. Using DAX I can calculate the CUSUMValue (I think) but how can this be done dynamically? I want the chart to update when I filter through months but it always has to start from 0 and count forwards from there to the end of the selected data.

Is this possible in the current version in PowerBI?

[Anonymous · Accepted solution]

Hi @BenFransen,

According to your description, you want to calculate dynamic value which based on "RiskScore" and "PatientDied" columns, right?

If that is a case, you can refer to belwo sample:

Measures:

CurrentValue =
var currPatientDied= LASTNONBLANK(Sheet2[PatientDied],[PatientDied])
Return
Max([RiskScore])*SWITCH(currPatientDied,FALSE(),1,TRUE(),-1)

CUSUMValue =
SUMX(FILTER(ALL(Sheet2),Sheet2[EventID]<=MAX([EventID])),[CurrentValue])

Create line chart:

Regards,

Xiaoxin Sheng