cancel
Showing results for
Did you mean: Frequent Visitor

## 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 Nulls and hence the values post that are appearing as Nulls.

I have tried If condition for the total column to use 0 in place of but it didn't help/.

I tried modifying the creating a measure to replace the original column by 0 in case of NULLs but the function didn't give me expeicted results.

I can't replace Nulls with 0s in power query as there are no rows in original source for these users since they didn't complete any cases.

Here is the original formula and the modified formulas I have tried so far to no use :

Cumulative cases Last month = CALCULATE (SUM(Monthlypayments[Total cases]),filter(ALLSELECTED( Calendar_dim), Calendar_dim[Date]<=maxx(Calendar_dim,dateadd(Calendar_dim[Date],-1,month))))+0

tried :

Cumulative Specialities LastMonthNull = VAR transmaxdate =
CALCULATE ( Max(Calendar_dim[Date]), ALL ( 'Calendar_dim' ) )
VAR spec =
TOTALYTD ( SUM ( Monthlypayments[Total cases] ), Calendar_dim[Date])
return
IF ( MIN ( 'Calendar_dim'[Date] ) <= transmaxdate, spec ,0 )

another option :

Cumulative Specialities LastMonthNull = CALCULATE (If(ISBLANK(Sum(Monthlypayments[Total cases])),0,sum(Monthlypayments[Total cases])),filter(ALL( Calendar_dim), Calendar_dim[Date]<= MAXX(Calendar_dim,DATEADD(Calendar_dim[Date],-1,MONTH))))

Here is the sample dataset :
 Month User Cases this month Cumuluative Output I get(cumulative last month) Output I want Jan A 10 Feb A 20 10 10 Mar A 40 30 30 Apr A 70 May A 70 Jan B 15 Feb B 30 15 15 Mar B 25 45 45 Apr B 20 70 70 May B 90

1 ACCEPTED SOLUTION  Super User

Hi @manish16111 ,

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.

4 REPLIES 4  Super User

Hi @manish16111 ,

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. Frequent Visitor

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 🙂  Community Champion

Use a disconnected table to for Year-Month row,  Frequent Visitor

@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!  