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

@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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

FrankAT
Super User II
Super User II

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

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

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

@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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors