Cumulative sum of last 4 weeks keeping slicers

[Anonymous]

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

[v-kkf-msft · Accepted 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.

[ryan_mayu]

@Anonymous 

could you pls provide the pbix file?