cumulative count of records/rows by different date columns

[Steve659]

Hi

I’ve been learning DAX but have the following problem, we have records within a database, and each record in called a lot, now I want to track the overall progress open and closed and total lots within the system

So I have measures for

Total Lots = COUNTROWS('QA Lots')

Open Lots = CALCULATE(COUNTROWS('QA Lots'), 'QA Lots'[StdStatus] = "Open")

Closed Lots = CALCULATE(COUNTROWS('QA Lots'), 'QA Lots'[StdStatus] = "Closed")

But this gives me the total numbers and I want the total numbers at any particular point in time, so I created the following measures

Cumulative Total = CALCULATE ( Count ('QA Lots'[LotId]), FILTER (ALL ('QA Lots'), 'QA Lots'[Dt_CrtdOn] <=MAX('QA Lots'[Dt_CrtdOn])))

Cumulative Closed = CALCULATE ( Count ('QA Lots'[LotId]), FILTER (ALL ('QA Lots'), 'QA Lots'[StdStatus] = "Closed" && 'QA Lots'[DT_Closed] <=MAX('QA Lots'[Dt_CrtdOn])))

Cumulative Open = [Cumulative Total]-[Cumulative Closed]

This worked and allowed me to create the following graph

But with the above measures I can’t use any slicers to filter this graph they just don’t work

So I did a little research and though keepfilters might work

I created the following measure for Total Lots

Cumulative Total = Calculate (Count ('QA Lots'[LotId]), KEEPFILTERS('QA Lots'[Dt_CrtdOn]<= (MAX('QA Lots'[Dt_CrtdOn]))))

Which seems to work and allows me to filter using a slicer

But when I tried to do the same for Closed

Cumulative Closed = CALCULATE ( Count ('QA Lots'[LotId]), KEEPFILTERS(FILTER (ALL ('QA Lots'), 'QA Lots'[StdStatus] = "Closed" && 'QA Lots'[DT_Closed] <=MAX('QA Lots'[Dt_CrtdOn]))))

It doesn’t work and I believe this is because keepfilters can’t handle multiple arguments, is this correct? Is there another way I can do this?

[v-yalanwu-msft · Accepted solution]

Hi, @Steve659 ;

You could change the ALL() to ALLSELECTED().

Cumulative Total = CALCULATE ( Count ('QA Lots'[LotId]), FILTER (ALLSELECTED ('QA Lots'), 'QA Lots'[Dt_CrtdOn] <=MAX('QA Lots'[Dt_CrtdOn])))

Cumulative Closed = CALCULATE ( Count ('QA Lots'[LotId]), FILTER (ALLSELECTED ('QA Lots'), 'QA Lots'[StdStatus] = "Closed" && 'QA Lots'[DT_Closed] <=MAX('QA Lots'[Dt_CrtdOn])))

Cumulative Open = [Cumulative Total]-[Cumulative Closed]

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[blackhawk]

Hi Yalan,

I too have similar use case and I have tried to use the formula discussed here to create the cumulative balanced for the open items vs closed items. Below is the DAX formula that I have used:

1) 

Cumulative RADAR Total =

CALCULATE(COUNT(RADAR_Table[Action ID]),

                FILTER(ALLSELECTED(RADAR_Table), RADAR_Table[Created Date] <= MAX(RADAR_Table[Created Date])))

2) 

Cumulative Radar Closed =

CALCULATE( COUNT(RADAR_Table[Action ID]),

                FILTER(ALLSELECTED(RADAR_Table),

                                                RADAR_Table[Action Status] = "Closed" &&

                                                RADAR_Table[Date Closed] <= MAX(RADAR_Table[Created Date])

                )

)

3) 

Cumulative RADAR Open = [Cumulative RADAR Total] - [Cumulative Radar Closed]

Below is the chart:

The data model of the two table is as such:

However, I have the following challenges and hope you can help out

1. I noticed that the chart only stop at 2022 but there is still closure of actions in 2023. I went to inspect the data and i see there is no new action item created in 2023. Is that the reason why it is not being shown and how to solve this?

 2. When i try to apply filter on the chart for one of the area, as there is no action created in 2019, there is no data shown for that year and it leaves a gap in that year (see snip below). Rightfully there should be cumulative bar in 2019 even though there is no new action created. Separately, i went to create another measure and add in a filter for that area to create a specific chart for that area and then it shows. However, it still doesn't reflect there are action items closed in 2023. May i know how to solve this?

Thanks a lot in advanced!

[amitchandak]

@Steve659 , Create a date table and join it with your date table and then use it on the axis and measure

Cumulative Total = CALCULATE ( Count ('QA Lots'[LotId]), FILTER (ALL ('Date'), 'QA Lots'[Date] <=MAX(''[Date])))

Cumulative Closed = CALCULATE ( Count ('QA Lots'[LotId]), FILTER (ALL ('QA Lots'), 'QA Lots'[StdStatus] = "Closed"), FILTER (ALL ('Date'), 'QA Lots'[Date] <=MAX(''[Date])) )

Cumulative Open = [Cumulative Total]-[Cumulative Closed]

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

[Steve659]

Thanks amitchandak, I'll give that a try