Count values cumulatively over time

[iangilsenan]

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

[amitchandak]

@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-Y...


Appreciate your Kudos.

[iangilsenan · Accepted solution]

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

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