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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!