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.

     

     

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

Use a disconnected table to for Year-Month row,

Anonymous's avatar
Anonymous
Not applicable
5 years ago

CNENFRNL - Thanks for your reply! I did implemented the same thing and created the disjoined table and the ytd measure as  below but it just gives me the calculations for that month rather than cumulative numbers. I still however have the calendar dim joined ? Could that be the reason ?

Cumulative cases =
VAR __ym = MAX ( YrMMM[ym] )
VAR __ym_max = CALCULATE ( MAX ( Monthlypayments[Yearmonth] ), ALLSELECTED( Monthlypayments) )
RETURN
IF ( MAX( YrMMM[ym]) <= __ym_max,
CALCULATE ( SUM ( Monthlypayments[Total case] ),
CALCULATETABLE ( DATESYTD ( Monthlypayments[datecompleted]), Monthlypayments[Yearmonth] <= __ym )
) ) 
Datecompleted column is defaulted to the 1st day of the month along with the cases completed for that month in the summary table I am using. Thanks for your help with this!