Cumulative sum between 2 distinct period

[Yazid]

Dear,

I have a column date data, namely 'MERGED-COST SAVINGS'[Start date]

Remark: This last column has date from 01/01/24 to 31/12/2026

I would like to construct a graph of cumulative sum for:

First hand: summing values 'MERGED-COST SAVINGS'[Full Year Forecast 2024]) BETWEEN the PERIOD 01/01/24 to 31/12/2024

Second hand: summing values 'MERGED-COST SAVINGS'[Estimated 12 month impact] BETWEEN the PERIOD 01/01/25 to 31/12/2026

I have written DAX code (It wrong) just for you to understand my logic.

Could you please let me know how to write a correct code ?

Thanks in advance for your support.

[Anonymous · Accepted solution]

Hi @Yazid 

Based on your needs, I have created the following table.

You can use the following measure to calculate the cumulative sum:

Measure = 
VAR select_date = SELECTEDVALUE('Table'[Date])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Date]<=select_date),'Table'[Value])

This is the result you want:

Best Regards,

Jayleny

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

[rajendraongole1]

Hi @Yazid create a cumulative sum of two different sets of data over specific periods. 

I am using the same dates as reference for summing values 'MERGED-COST SAVINGS'[Full Year Forecast 2024]) BETWEEN the PERIOD 01/01/24 to 31/12/2024.

Cumulative Sum 2024 =
VAR StartDate = DATE(2024, 1, 1)
VAR EndDate = DATE(2024, 12, 31)
RETURN
CALCULATE(
SUM('MERGED-COST SAVINGS'[Full Year Forecast 2024]),
FILTER(
'MERGED-COST SAVINGS',
'MERGED-COST SAVINGS'[Start date] >= StartDate &&
'MERGED-COST SAVINGS'[Start date] <= EndDate
),
FILTER(
ALL('MERGED-COST SAVINGS'[Start date]),
'MERGED-COST SAVINGS'[Start date] <= EndDate
)
)

create another measure for summing values 'MERGED-COST SAVINGS'[Estimated 12 month impact] BETWEEN the PERIOD 01/01/25 to 31/12/2026

Cumulative Sum 2025_2026 =
VAR StartDate = DATE(2025, 1, 1)
VAR EndDate = DATE(2026, 12, 31)
RETURN
CALCULATE(
SUM('MERGED-COST SAVINGS'[Estimated 12 month impact]),
FILTER(
'MERGED-COST SAVINGS',
'MERGED-COST SAVINGS'[Start date] >= StartDate &&
'MERGED-COST SAVINGS'[Start date] <= EndDate
),
FILTER(
ALL('MERGED-COST SAVINGS'[Start date]),
'MERGED-COST SAVINGS'[Start date] <= EndDate
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Yazid]

Dear,

Thanks for your reply. I've created the first function CumulativeSumFcst2024 as you mentionned.

However, the results are not cumulative (not a strict increasing function)

Note that the blue line is the current Cumulative fonction which still take the entire period of time.

Here below its DAX code:

Digging the values, your formula do not perform a cumulative sum , have a look here below:

Here below is the structure of the columns in question: