topic Re: Help with running totals (cumulative daily attendance) in DAX Commands and Tips

Help with running totals (cumulative daily attendance)

JoKelly3 — Wed, 31 Jan 2024 12:45:28 GMT

I am fairly new to Power Bi so forgive my ignorance. I have some student attendance data and I would like to see it cumulatively (the reason for this is I have another calculation to work out the number of students per week that are below 90% attendance which would run from the cumulative attendance). I need a way to calculate the 'cumulative present' and 'cumulative possible' for each student for each day. I've tried a few different ways so far but not had any luck with any of them.

I would be massively grateful for some help!- Thanks, Jo

Here is my data:

Date	Unique ID	Present	Possible	Cumulative Present	Cumulative Possible	Attendance
05/09/2023	101	1	1	1	1	cumulative present/cumulative possible
05/09/2023	102	0	1	0	1	cumulative present/cumulative possible
05/09/2023	103	1	1	1	1	cumulative present/cumulative possible
05/09/2023	104	1	1	1	1	cumulative present/cumulative possible
06/09/2023	101	1	1	2	2	cumulative present/cumulative possible
06/09/2023	102	1	1	1	2	cumulative present/cumulative possible
06/09/2023	103	1	1	2	2	cumulative present/cumulative possible
06/09/2023	104	1	1	2	2	cumulative present/cumulative possible

Re: Help with running totals (cumulative daily attendance)

JoKelly3 — Wed, 31 Jan 2024 13:57:53 GMT

I have now managed to create a grouped index column in Power Query but I still need to create the cumulative totals

Re: Help with running totals (cumulative daily attendance)

v-weiyan1-msft — Thu, 01 Feb 2024 03:19:21 GMT

Hi JoKelly3 ,

Based on the sample and description you provided,
you might consider using the following code to create two Calculated Columns.

Cumulative Present = CALCULATE(SUM('Table'[Present]), FILTER(ALLEXCEPT('Table','Table'[Unique OD]), 'Table'[Date] <= EARLIER('Table'[Date])) )

Cumulative Possible = CALCULATE(SUM('Table'[Possible]), FILTER(ALLEXCEPT('Table','Table'[Unique OD]), 'Table'[Date] <= EARLIER('Table'[Date])) )

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.