Forum Discussion

lg01's avatar
lg01
Icon for Advocate III rankAdvocate III
1 year ago
Solved

Running total per category and date

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

 

DateFacilityNameMaxProductionMinProductionMaxPerMonth
1/1/2024 0:00Facility1152051047120
2/1/2024 0:00Facility1152051044080
3/1/2024 0:00Facility1152051047120
4/1/2024 0:00Facility1152051045600
5/1/2024 0:00Facility1152051047120
6/1/2024 0:00Facility1152051045600
7/1/2024 0:00Facility1152051047120
8/1/2024 0:00Facility1152051047120
9/1/2024 0:00Facility1152051045600
10/1/2024 0:00Facility1152051047120
11/1/2024 0:00Facility1152051045600
12/1/2024 0:00Facility1152051047120
1/1/2025 0:00Facility11393.638305.17543202.778
2/1/2025 0:00Facility11393.638305.17539021.864
3/1/2025 0:00Facility11393.638305.17543202.778
4/1/2025 0:00Facility11393.638305.17541809.14
5/1/2025 0:00Facility11393.638305.17543202.778
6/1/2025 0:00Facility11393.638305.17541809.14
7/1/2025 0:00Facility11393.638305.17543202.778
8/1/2025 0:00Facility11393.638305.17543202.778
9/1/2025 0:00Facility11393.638305.17541809.14
10/1/2025 0:00Facility11393.638305.17543202.778
11/1/2025 0:00Facility11393.638305.17541809.14

 

The result will look like this:

 

DateFacilityNameMaxProductionMinProductionMaxPerMonthRunningTotalPerFacilityMonth
1/1/2024 0:00Facility115205104712047120
2/1/2024 0:00Facility115205104408091200
3/1/2024 0:00Facility1152051047120138320
4/1/2024 0:00Facility1152051045600183920
5/1/2024 0:00Facility1152051047120231040
6/1/2024 0:00Facility1152051045600276640
7/1/2024 0:00Facility1152051047120323760
8/1/2024 0:00Facility1152051047120370880
9/1/2024 0:00Facility1152051045600416480
10/1/2024 0:00Facility1152051047120463600
11/1/2024 0:00Facility1152051045600509200
12/1/2024 0:00Facility1152051047120556320
1/1/2025 0:00Facility11393.638305.17543202.77843202.778
2/1/2025 0:00Facility11393.638305.17539021.86482224.642
3/1/2025 0:00Facility11393.638305.17543202.778125427.42
4/1/2025 0:00Facility11393.638305.17541809.14167236.56
5/1/2025 0:00Facility11393.638305.17543202.778210439.338
6/1/2025 0:00Facility11393.638305.17541809.14252248.478
7/1/2025 0:00Facility11393.638305.17543202.778295451.256
8/1/2025 0:00Facility11393.638305.17543202.778338654.034
9/1/2025 0:00Facility11393.638305.17541809.14380463.174
10/1/2025 0:00Facility11393.638305.17543202.778423665.952
11/1/2025 0:00Facility11393.638305.17541809.14465475.092
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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's avatar
    v-karpurapud
    1 year ago

    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

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    1 year ago

    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