Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative sum of last 4 weeks keeping slicers

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.

1.png

 

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.

2.png

 

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

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

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 )
)

image.png

 

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.

 

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

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 )
)

image.png

 

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
Super User
Super User

@Anonymous 

 

could you pls provide the pbix file?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.