cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manish16111
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. 

 

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 :
MonthUserCases this monthCumuluative Output I get(cumulative last month)Output I want
JanA10  
FebA201010
Mar A403030
AprA  70
MayA  70
JanB15  
FebB301515
Mar B254545
AprB207070
MayB  90
     
 
1 ACCEPTED SOLUTION
ERD
Super User
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]))

 

ERD_2-1623678523324.png

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

 

 

View solution in original post

4 REPLIES 4
ERD
Super User
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]))

 

ERD_2-1623678523324.png

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

 

 

View solution in original post

manish16111
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 🙂

CNENFRNL
Community Champion
Community Champion

Use a disconnected table to for Year-Month row,

Screenshot 2021-06-14 132058.png

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors