Rolling cumulative Count

[Anonymous]

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.

[edhans · Accepted solution]

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.

[edhans]

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!

[edhans]

Hi @Anonymous - can you explain your expected results a bit better? Here is what I have so far:

1. Added a Week Starting Date to my date table and it is set for Monday - Day.Monday.
2. Added that to the table visual
3. 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.

[Anonymous]

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.

[edhans]

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.

[Anonymous]

My bad, yes you are right while I am applying filter I counted previous week record as well. 

[edhans · Accepted solution]

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.

[Anonymous]

I was restricted to copy the workbook, can please share screen captures so I can refer. I was able to create date table.

[edhans]

What do you mean you are restricted? I just checked. It opens fine. You should see this - just click download. It is a PBIX Power BI file, not a workbook.

[Anonymous · Accepted solution]

Sorry for the delay, restriction at my end. Thanks for the help it worked well.