Forum Discussion
Rolling cumulative Count
I am trying to calculate rolling cumulative count by weekstart (from monday) having a context filter but results are not as expected, please help how to work with DAX to get accurate result. Below it the sample data
| Item | Initiated | Mode | State |
| MOCK05129269 | 10/1/2020 | Run | Finish |
| MOCK05129382 | 10/1/2020 | Production | Progress |
| MOCK05137817 | 10/5/2020 | UAT | Progress |
| MOCK05148189 | 10/6/2020 | Production | Audit |
| MOCK05148251 | 10/6/2020 | Run | Finish |
| MOCK05154676 | 10/7/2020 | Production | Finish |
| MOCK05156745 | 10/7/2020 | Run | Progress |
| MOCK05161438 | 10/8/2020 | Production | Finish |
| MOCK05161971 | 10/8/2020 | Run | Progress |
| MOCK05166040 | 10/8/2020 | Production | Audit |
| MOCK05166041 | 10/8/2020 | Run | Finish |
| MOCK05172118 | 10/9/2020 | Run | Finish |
| MOCK05176326 | 10/12/2020 | Production | Progress |
| MOCK05178769 | 10/13/2020 | Production | Audit |
| MOCK05185646 | 10/13/2020 | Run | Finish |
| MOCK05192145 | 10/14/2020 | Run | Finish |
| MOCK05192665 | 10/14/2020 | Production | Finish |
| MOCK05206922 | 10/16/2020 | Run | Finish |
| MOCK05211641 | 10/19/2020 | Production | Progress |
| MOCK05215977 | 10/20/2020 | Production | Audit |
| MOCK05225207 | 10/21/2020 | Production | Audit |
| MOCK05231344 | 10/22/2020 | Production | Finish |
| MOCK05238856 | 10/23/2020 | Production | Finish |
| MOCK05211642 | 10/26/2020 | Production | Progress |
| MOCK05215979 | 10/27/2020 | Production | Audit |
| MOCK05225299 | 10/28/2020 | Production | Audit |
| MOCK05231355 | 10/29/2020 | Production | Finish |
| MOCK05238859 | 10/30/2020 | Production | Finish |
and I am trying to achieve
| Week start (Monday) | Running |
| Week of 10/05/2020 | 3 |
| Week of 10/12/2020 | 5 |
| Week of 10/19/2020 | 8 |
| Week of 10/26/2020 | 11 |
Sinceely,
Krishna.
Ok Anonymous - see if this works. It returns the data on the right side of the image. It is not affected by the slicers, just showing that it returns the same results.
Running Total = COUNTX( FILTER( 'Table', 'Table'[Mode] = "Production" && ('Table'[State] = "Progress" || 'Table'[State] = "Audit") ), 'Table'[Item] )Note: you must have a date table. Grab the PBIX file I linked to above in my first post. It is updated with this new data and the measure.
9 Replies
Hi Anonymous - can you explain your expected results a bit better? Here is what I have so far:
- Added a Week Starting Date to my date table and it is set for Monday - Day.Monday.
- Added that to the table visual
- Added a measure that is simply COUNTROWS('Table') which counts records in each week.
You can see my file here. It is not yet your expected results though, so explain how you got 3 for Oct 5, but nothing for Sept 28 with your source data, as an example. In other words, where are the 2 Oct 1 amounts going? Those are before Oct 5 week starting.
Sorry, I should have put additional details in originalpost. Ideally I am hoping to show total count of items filter by Mode is Production and State in (Progress and Audit). As per data shared for Week 10/05/2020 (start from 10/05/2020 to 10/11/2020) total items are 12 but if I apply mode and state filters it come down to 3. Imagin the three items status not changed and new items created in following weeks. So the next week would be 10/12/2020 (10/12/2020 to 10/18/2020) total open items 6 but if I apply mode and state filters it come down to 2. so till current point in time (10/12/2020 weekstart) total open items are 2(current week)+3(previous weeks, infact I have two years historical data) is 5. So samething applies to following week 10/19/2020 (10/19/2020 to 10/23/2020) total items created 5 but if i apply mode and state filter it come down to 3 . So total open items till current point in time is (3+2+3). Ideally it is rolling cumulative items from entire data set till current. Hope I explained in detailed. Sorry I forgot to add Sept 28 weekstart.
How are you getting 3 for Oct 5 I get 2. I want a really good explanation of how the expected results are to be calculated before I try to get the DAX right. Here I am using filters. I have set MODE to Production, and State to Audit and Progress. Only 2 records show up, not 3.
I would prefer you show me using some mockups in excel where you can post a screeshot, not a long paragraph I have to disect and parse, if that is possible. Just be very very clear on how you get 3 for Oct 5 week with the given data.
Great Anonymous glad it worked out. I hope you don't mind but I marked my answer as the solution. I think you accidentally marked your reply as the answer vs the actual answer.
Any additional questions, let us know!