Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative Sum including Nulls

Hi Gurus,  I am calculating cumulative sum for total cases worked upon by employees. However, if in a month an employee doesn't have any cases because of the join with dim_Calendar I am getting Null...
  • ERD's avatar
    ERD
    5 years ago

    Hi Anonymous ,

    I don't know all the prerequisites and how your raw data looks like, but assuming you have a proper Calendar table ("Date" in my example) connected to your Data table by Date column:

    Measure 1:

     

    CasesAmt = COALESCE(SUM(ASGMT[Cases]),0)

     

    Measure 2:

     

    CasesYTD = CALCULATE([CasesAmt], DATESYTD('Date'[Date]))

     

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

     

     

ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous ,

I don't know all the prerequisites and how your raw data looks like, but assuming you have a proper Calendar table ("Date" in my example) connected to your Data table by Date column:

Measure 1:

 

CasesAmt = COALESCE(SUM(ASGMT[Cases]),0)

 

Measure 2:

 

CasesYTD = CALCULATE([CasesAmt], DATESYTD('Date'[Date]))

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you ERD! Looks like I had an issue with my Data modelling. I had selected both as cross filtering direction which was causing troubles. As soon as I corrected the cross filtering direction, my previous formula and the one suggested by you started to work 🙂 Thanks heaps for your help 🙂