Cumulative Sum including Nulls

[Anonymous]

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. 

Thanks in advance !

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

[ERD · Accepted solution]

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]

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 🙂

[CNENFRNL]

Use a disconnected table to for Year-Month row,

[Anonymous]

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