Cumulative Count

[Gkourtoglou]

Good sirs. I have an excel file with Columns "Creation Date" (date format), "Week Number" (General), "Ticket ID" (General), "Year" (General) among plenty of others as seen on my first image below, and I import this excel file on Power BI.

Excel Description

Each row is one request from a client.

To find "Week Number", I use =TEXT(ISOWEEKNUM([@[Creation Date]]);"00")

To find "Year", I use =YEAR([@[Creation Date]])

Power BI

I count the column "Ticket ID" with axis "Week Number" in order to get how many request I have from clients each week and use filters accordingly.

Cumulative Count

I would like to create a graph like the second image below, where Axis is Week Number and each value is the cumulative count of each week, comparing year by year.

Week by Week count

Cumulative Count

After numerous months of research and fails, I humbly ask you senseis for some guidance 🙂

Best regards,

George

[Gkourtoglou]

Thanks a lot guys for you assistance.

@richbenmintz your solution is ideal.

Best regards,

George

[richbenmintz · Accepted solution]

Hi @Gkourtoglou ,

you can use a measure like 

Weekly Cummulative = CALCULATE(sum('Table'[Tickets]), FILTER(ALLEXCEPT('Table', 'Table (3)'[Year]), 'Table'[Week] <= MAX('Table (3)'[Week]))) 

you would have to change the sum to a countrows given you data, i just mocked it up with a sum.

results below

Hope this helps,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[amitchandak]

@Gkourtoglou , refer if these can help

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

[Greg_Deckler]

@Gkourtoglou - There is a running total quick measure that might work. Also, you can get there if you have a measure using measure aggregation. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

You would put FILTER in to filter in only the stuff that is "before".

If none of that helps, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.