Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Steve659
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

Steve659_0-1654035024618.png

 

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
v-yalanwu-msft
Community Support
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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
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.

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:
blackhawk_0-1693443799220.png

 

The data model of the two table is as such:

 

blackhawk_1-1693444163974.png

 

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?

 

blackhawk_2-1693444396541.png

 

 

Thanks a lot in advanced!

amitchandak
Super User
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]

 

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

Thanks amitchandak, I'll give that a try

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors