Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | ||
Solved! Go to 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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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 🙂
Use a disconnected table to for Year-Month row,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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 ?
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
56 | |
28 | |
20 | |
16 |