Forum Discussion

samdep's avatar
samdep
Advocate II
5 years ago
Solved

Running Total

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's avatar
    HashamNiaz
    5 years ago

    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

2 Replies

  • HashamNiaz's avatar
    HashamNiaz
    Solution Sage
    5 years ago

    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