Forum Discussion
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 :
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 :
| 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 | ||
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.
4 Replies
Use a disconnected table to for Year-Month row,
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) )RETURNIF ( 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!
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.
