Forum Discussion

CornelisV's avatar
CornelisV
Helper IV
1 year ago
Solved

Cumulative sum from different categories in same plot/table

Dear all,

 

we are struggling for finding the best method to apply cumulative sum from two different categories and putting into the same table and plot.

As Main source, here is the table:

the table is split into two different category: 2025 and 2024.

The desired answer is:

Column Day has been created using this DAX in the Calendar table:

Days = FORMAT(DATE(YEAR('Calendar'[Datum]),12,31) - DATE(YEAR('Calendar'[Datum]),1,1), "General Number") -
            FORMAT(DATE(YEAR('Calendar'[Datum]),12,31) - 'Calendar'[Date], "General Number") +1
   
This Calendar table is linked with the Main source table (see above) and I tried to apply cumulative sum using this Measure:
**bleep** sum =
  CALCULATE(
     SUM('Source'[Score]),
     'Calendar'[Date] <= MAX('Calendar'[Date])
  )
 
 
As you may see, the cumulative sum is not properly calculated over number of days. 
Could you please demonstrate how to solve this?
 
Here is the excel file data:
dateScore
01-01-20255
01-01-20258
02-01-20254
03-01-20253
04-01-20255
04-01-20256
05-01-20254
02-01-20244
02-01-20248
02-01-20241
03-01-20241
04-01-20248
05-01-20243

 

Best regards,

 

Cornelis

 

 

Need Help
  • Sachin001's avatar
    Sachin001
    1 year ago

    CornelisV Find steps below: 

     

    Created a  table using your data:

     

    for year 2024 cumulative sum will start as 13 not 19 as 8+1+4 = 13

     

     

     

    1. Create new column as a Year and Day. 

    2. Create cummulative Column with below dax code:

     

    Column Running Total =

     CALCULATE(
          Sum('Table (4)'[Score]),
            'Table (4)'[Date] <= EARLIER('Table (4)'[Date]),
            'Table (4)'[Year] = EARLIER('Table (4)'[Year]),
            ALL('Table (4)')
            )
     
     
     
    Result :  

     

    let me know if this helps

     

     

6 Replies

  • mh2587's avatar
    mh2587
    Super User
    1 year ago
    Cumulative Score = // Try this one might help you
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentDay = MAX('Calendar'[DayOfYear])
RETURN
CALCULATE(
    SUM('Source'[Score]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year] = SelectedYear &&
        'Calendar'[DayOfYear] <= CurrentDay
    )
)
    • CornelisV's avatar
      CornelisV
      Helper IV
      1 year ago

      Dear mh2587 , thank you for your prompt answer.

      The DayOf Year, could you please clarify?

       

      Best regards,

       

      Cornelis

       

      • mh2587's avatar
        mh2587
        Super User
        1 year ago

        If you don't have DayOfYear Column you can create like following:

        DayOfYear = DATEDIFF(STARTOFYEAR('Calendar'[Date]), 'Calendar'[Date], DAY) + 1
  • Sachin001's avatar
    Sachin001
    Frequent Visitor
    1 year ago

    CornelisV Find steps below: 

     

    Created a  table using your data:

     

    for year 2024 cumulative sum will start as 13 not 19 as 8+1+4 = 13

     

     

     

    1. Create new column as a Year and Day. 

    2. Create cummulative Column with below dax code:

     

    Column Running Total =

     CALCULATE(
          Sum('Table (4)'[Score]),
            'Table (4)'[Date] <= EARLIER('Table (4)'[Date]),
            'Table (4)'[Year] = EARLIER('Table (4)'[Year]),
            ALL('Table (4)')
            )
     
     
     
    Result :  

     

    let me know if this helps

     

     
    • CornelisV's avatar
      CornelisV
      Helper IV
      1 year ago

      Hi Sachin001 , thank you for your solution. That is is exact what I'm looking for. I used the Measure option, but you have applied the cumulative sum directly in the table (4). That is a different approach but it works.

      Have a great day,

      Cornelis