cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeepakJha23
Frequent Visitor

Cumulative Count by Week

Hi Power BI Users,

 

I am having an issue with Cumulative Count by week. Here is the sample data i am working on : 

 

Sample DataSample Data

This shows the tickets opened on a date and its corresponding week_number.

 

We want to plot the data on the weeknum level with Cumulative count like this : 

 

Output1Output1

Any help would be deeply appreciated.

 

Thanks,

 

 

@Greg_Deckler @mahoneypat @edhans 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@DeepakJha23 - You could create a measure like this:

Cumulative Count =
  VAR __Week = MAX('Table'[Week_Num(Date)])
RETURN
  SUMX(FILTER('Table',[Week_Num(Date)]<=__Week),[TicketCount])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

FrankAT
Community Champion
Community Champion

Hello @DeepakJha23

here's my solution. It is very closed to @Greg_Deckler solution:

02-09-_2020_13-26-14.png

Sum of Ticket Count = SUM('Table'[Ticket Count])

Cumulative Count = 
CALCULATE(
    [Sum of Ticket Count],
    FILTER(
        ALL('Table'),
        'Table'[Week_Num(Date)] <= MAX('Table'[Week_Num(Date)])
    )
)

With kind greetings from the city where the legend of the 'Pied Piper de Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

3 REPLIES 3
FrankAT
Community Champion
Community Champion

Hello @DeepakJha23

here's my solution. It is very closed to @Greg_Deckler solution:

02-09-_2020_13-26-14.png

Sum of Ticket Count = SUM('Table'[Ticket Count])

Cumulative Count = 
CALCULATE(
    [Sum of Ticket Count],
    FILTER(
        ALL('Table'),
        'Table'[Week_Num(Date)] <= MAX('Table'[Week_Num(Date)])
    )
)

With kind greetings from the city where the legend of the 'Pied Piper de Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

amitchandak
Super User
Super User

@DeepakJha23 , as you have date you can use a date calendar and measure like this  (You can have week table with week date)

. Make sure week is taken from week table

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(Week,Week[Week] <=maxx(Week,Week[Week])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Week),Week[Week] <=maxx(Week,Week[Week])))

 

also refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User
Super User

@DeepakJha23 - You could create a measure like this:

Cumulative Count =
  VAR __Week = MAX('Table'[Week_Num(Date)])
RETURN
  SUMX(FILTER('Table',[Week_Num(Date)]<=__Week),[TicketCount])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!