Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to calculate cumulative sum of tickets in last for weeks. So week 23 displays sum of weeks 23+22+21+20, week 22 displays sum of weeks 22+21+20+19, etc
The dataset is transactional (Date, TicketID, Status). I managed to make a summary table of total tickets per week and calculate the cumulative value of last 4 weeks.
However, now the dataset has changed. The data broken down to Department and Sector (Date, TicketID, Status, Department, Sector), so i would like to allow users to slice data and calculate cumulative values based on WeekNum, Department and Sector.
Could you please help amending the DAX formula so it allows slicing by Depratment and Sector?
CALCULATE( SUM(CumTickets[Defects]), DATESINPERIOD( 'Calendar'[Dates], LASTDATE( 'Calendar'[Dates]), -22, DAY ))
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
Try the following formula:
Measure =
CALCULATE(
SUM(CumTickets[#Tickets]),
ALLEXCEPT( CumTickets, CumTickets[Department], CumTickets[Sector] ),
DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'CumTickets'[Start Of Week]), -22, DAY )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try the following formula:
Measure =
CALCULATE(
SUM(CumTickets[#Tickets]),
ALLEXCEPT( CumTickets, CumTickets[Department], CumTickets[Sector] ),
DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'CumTickets'[Start Of Week]), -22, DAY )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
could you pls provide the pbix file?
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |