Cumulative sum from different categories in same plot/table

[CornelisV]

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:

date	Score
01-01-2025	5
01-01-2025	8
02-01-2025	4
03-01-2025	3
04-01-2025	5
04-01-2025	6
05-01-2025	4
02-01-2024	4
02-01-2024	8
02-01-2024	1
03-01-2024	1
04-01-2024	8
05-01-2024	3

Best regards,

Cornelis

[Sachin001 · Accepted solution]

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

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

[mh2587]

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]

Dear @mh2587 , thank you for your prompt answer.

The DayOf Year, could you please clarify?

Best regards,

Cornelis

[mh2587]

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

DayOfYear = DATEDIFF(STARTOFYEAR('Calendar'[Date]), 'Calendar'[Date], DAY) + 1

[CornelisV]

Hello @mh2587 , thank you!

I have just applied in Power BI and here is the result:

As you can see, no cumulative calculation. The score 1-2-2025 must be 13, at 02-01-2025 17, at 03-01-2025 20. Something goes wrong.

Any idea?

Best regards,

Cornelis