cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## cumulative count of records/rows by different date columns

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?

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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)

2)

)
)

3)

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?

Super User

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

Regular Visitor

Thanks amitchandak, I'll give that a try

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors