Running Total

[samdep]

Hi Everyone - 

I've created a calculated column that looks at the count of days from a client request to intake of that client - off of two date fields.

I then created a measure for a running total/cume by day -- but on days where there are zero intakes, the first value is populating in my table.

My measure:

Cume Intakes = 

CALCULATE(COUNT('Assessment'[Connection to Intake Diff in Days]),

FILTER(ALLSELECTED('Assessment'[Connection to Intake Diff in Days]),

'Assessment'[Connection to Intake Diff in Days] <= MAX('Assessment'[Connection to Intake Diff in Days])))

Basically, the output looks like the below -- In Row/Day 4, I'd prefer it either be blank or repeat the previous value - since no additional intakes occurred on Day 4, but it is instead returning Day 0's value... The cume is not impacted, so it's just a bit odd.

Day  Intake Count  Cume

0      16        16

1      64        80

2      37        117

3      33        150

4                  16

5      23        173

Appreciate any feedback on my probably not-great DAX code! Thank you!

[HashamNiaz · Accepted solution]

Hi !

You cna try using ISBLANK() function to get desired output;

Cume Intakes = 
IF ( ISBLANK('Assessment'[Intake Count])
    , BLANK()
    , CALCULATE(COUNT('Assessment'[Connection to Intake Diff in Days]),
        FILTER(ALLSELECTED('Assessment'[Connection to Intake Diff in Days]),
        'Assessment'[Connection to Intake Diff in Days] <= MAX('Assessment'[Connection to Intake Diff in Days])))
)

Regards,

Hasham

[samdep]

@HashamNiaz that worked perfectly, thank you!