Running total per category and date

[lg01]

I have the data below. I need to add a calculated column (or Measure) to get the running total of MaxPerMonth based on the year (Date) and the FacilityName. In other words, I need it to restart for each new year and also each Facility. So it will restart when it find a new facility or a new year in a facility. I tried several Iterations of this to no avail. Any ideas how to resolve this would be appreciated:

CALCULATE(sum('Table'[MaxPerMonth]),filter(Allexcept('Table','Table'[FacilityName]), 'Table'[Date] <= Earlier('Table'[Date]) ))

Date	FacilityName	MaxProduction	MinProduction	MaxPerMonth
1/1/2024 0:00	Facility1	1520	510	47120
2/1/2024 0:00	Facility1	1520	510	44080
3/1/2024 0:00	Facility1	1520	510	47120
4/1/2024 0:00	Facility1	1520	510	45600
5/1/2024 0:00	Facility1	1520	510	47120
6/1/2024 0:00	Facility1	1520	510	45600
7/1/2024 0:00	Facility1	1520	510	47120
8/1/2024 0:00	Facility1	1520	510	47120
9/1/2024 0:00	Facility1	1520	510	45600
10/1/2024 0:00	Facility1	1520	510	47120
11/1/2024 0:00	Facility1	1520	510	45600
12/1/2024 0:00	Facility1	1520	510	47120
1/1/2025 0:00	Facility1	1393.638	305.175	43202.778
2/1/2025 0:00	Facility1	1393.638	305.175	39021.864
3/1/2025 0:00	Facility1	1393.638	305.175	43202.778
4/1/2025 0:00	Facility1	1393.638	305.175	41809.14
5/1/2025 0:00	Facility1	1393.638	305.175	43202.778
6/1/2025 0:00	Facility1	1393.638	305.175	41809.14
7/1/2025 0:00	Facility1	1393.638	305.175	43202.778
8/1/2025 0:00	Facility1	1393.638	305.175	43202.778
9/1/2025 0:00	Facility1	1393.638	305.175	41809.14
10/1/2025 0:00	Facility1	1393.638	305.175	43202.778
11/1/2025 0:00	Facility1	1393.638	305.175	41809.14

The result will look like this:

Date	FacilityName	MaxProduction	MinProduction	MaxPerMonth	RunningTotalPerFacilityMonth
1/1/2024 0:00	Facility1	1520	510	47120	47120
2/1/2024 0:00	Facility1	1520	510	44080	91200
3/1/2024 0:00	Facility1	1520	510	47120	138320
4/1/2024 0:00	Facility1	1520	510	45600	183920
5/1/2024 0:00	Facility1	1520	510	47120	231040
6/1/2024 0:00	Facility1	1520	510	45600	276640
7/1/2024 0:00	Facility1	1520	510	47120	323760
8/1/2024 0:00	Facility1	1520	510	47120	370880
9/1/2024 0:00	Facility1	1520	510	45600	416480
10/1/2024 0:00	Facility1	1520	510	47120	463600
11/1/2024 0:00	Facility1	1520	510	45600	509200
12/1/2024 0:00	Facility1	1520	510	47120	556320
1/1/2025 0:00	Facility1	1393.638	305.175	43202.778	43202.778
2/1/2025 0:00	Facility1	1393.638	305.175	39021.864	82224.642
3/1/2025 0:00	Facility1	1393.638	305.175	43202.778	125427.42
4/1/2025 0:00	Facility1	1393.638	305.175	41809.14	167236.56
5/1/2025 0:00	Facility1	1393.638	305.175	43202.778	210439.338
6/1/2025 0:00	Facility1	1393.638	305.175	41809.14	252248.478
7/1/2025 0:00	Facility1	1393.638	305.175	43202.778	295451.256
8/1/2025 0:00	Facility1	1393.638	305.175	43202.778	338654.034
9/1/2025 0:00	Facility1	1393.638	305.175	41809.14	380463.174
10/1/2025 0:00	Facility1	1393.638	305.175	43202.778	423665.952
11/1/2025 0:00	Facility1	1393.638	305.175	41809.14	465475.092

[ryan_mayu · Accepted solution]

@lg01 

you can try this

Column = sumx(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&'Table'[FacilityName]=EARLIER('Table'[FacilityName])&&'Table'[MaxProduction]=EARLIER('Table'[MaxProduction])),'Table'[MaxPerMonth])

[v-karpurapud · Accepted solution]

Hi @lg01 

Could you please confirm if your query have been resolved the solution provided by @ryan_mayu ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[ryan_mayu · Accepted solution]

@lg01 

you can try this

Column = sumx(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&'Table'[FacilityName]=EARLIER('Table'[FacilityName])&&'Table'[MaxProduction]=EARLIER('Table'[MaxProduction])),'Table'[MaxPerMonth])