Forum Discussion
Count values cumulatively over time
We are using a few Power BI Reports in our high schools and are looking for help with counting values cumulatively over time in a Matrix table in a Power BI report.
I’ve provided simplified tables below and our desired outcome.
PUPIL TABLE
PupilId | Name |
1 | Paul Smith |
2 | Lisa White |
3 | John Brown |
Each pupil has an attendance mark record each day
ATTENDANCE TABLE
PupilID | MarkDate | Mark | Description | Category |
1 | 01/09/2019 | P | Present | Present |
2 | 01/09/2019 | P | Present | Present |
3 | 01/09/2019 | P | Present | Present |
1 | 02/09/2019 | P | Present | Present |
2 | 02/09/2019 | M | Medical | Absent |
3 | 02/09/2019 | P | Present | Present |
1 | 03/09/2019 | P | Present | Present |
2 | 03/09/2019 | I | Illness | Absent |
3 | 03/09/2019 | P | Present | Present |
We created a measure which counts the present marks:
COUNTROWS(FILTER(ATTENDANCE,[Category]="Present"))
But when we use this as the Value in a Matrix table (with MarkDate as column header and Name as Row Header), it just returns a count on each day.
We need the Present marks to be counted cumulatively over time, i.e.
01/09/2019
01/09/2019 to 02/09/2019
01/09/2019 to 03/09/2019
Like this example:
Name | 01/09/2019 | 02/09/2019 | 03/09/2019 |
Paul Smith | 1 | 2 | 3 |
Lisa White | 1 | 1 | 1 |
John Brown | 1 | 2 | 3 |
Having consulted Google we think it may need to use the EARLIER function but we haven’t used that before so would appreciate some advice.
Thanks
Thanks amitchandak.
I managed to solve it without creating a Dates table and using the following measure:
Cumulative Present =CALCULATE (COUNTAX ( FILTER ( ATTENDANCE,[Category] = "Present" ), [Category] ),FILTER (ALL ( ATTENDANCE[MarkDate] ),ATTENDANCE[MarkDate] <= MAX ( ATTENDANCE[MarkDate] )))
3 Replies
- amitchandak
Super User
iangilsenan , Not very clear. Typically this how we get cumulative with date calendar
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date]))) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Thanks amitchandak.
I managed to solve it without creating a Dates table and using the following measure:
Cumulative Present =CALCULATE (COUNTAX ( FILTER ( ATTENDANCE,[Category] = "Present" ), [Category] ),FILTER (ALL ( ATTENDANCE[MarkDate] ),ATTENDANCE[MarkDate] <= MAX ( ATTENDANCE[MarkDate] )))
v-yingjlCommunity Support
Hi iangilsenan ,
Thanks for your kindly sharing your solution and glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.
Best Regards,
Yingjie Li
