Help with calculating cumulative change and % in Power BI with multiple months selected

[Muska_Sukhija]

Hi everyone,

I am working on a Power BI dashboard with headcount data and NOT SALES, and I need help calculating cumulative change and cumulative % change when multiple months are selected in the slicer.

Here’s a sample of the data:

The dataset contains daily headcount data from 2019 to 2024.

Problem Statement:

I want to create a table matrix like this:

The table should show the cumulative change and cumulative % change, where the data is based on average headcount, and the slicer allows the selection of multiple months. I already have a measure to calculate average headcount when multiple months are selected.

Question:

• How can I calculate the cumulative change and cumulative % change when selecting multiple months in the slicer?
• What DAX measures would be needed to achieve this?

Any insights or advice would be greatly appreciated!

[Anonymous · Accepted solution]

Hi @Muska_Sukhija ,

Based on the description, creating the new table to calcualte average count.

Table 2 = 
SUMMARIZE(
    'Table',
    'Table'[Year],
    "AvgHecount", AVERAGE('Table'[Headcount])
)

Then, creating the index column in power query.

Creating new column to calculate the change.

Change in preivous year = IF('Table 2'[Index] = 1, 0, [AvgHecount] - LOOKUPVALUE('Table 2'[AvgHecount], [Index], [Index] - 1))

Creating the new measure to calculate cumulative change.

Cumulative change = 
CALCULATE(
    SUM('Table 2'[Change in preivous year]),
    FILTER(ALL('Table 2'), 'Table 2'[Index] <= MAX('Table 2'[Index])
    )
)

Besides, creating the new measure to calucalte %cumulative change and change the percentage format.

Cumulative change% = [Cumulative change] / 100

What's more, if you want to calculate cumulative, you need to use sum function. If you select multiple months, power bi cannot dynamically generate calculated columns based on slicer values. There would be no way to calculate the cumulative.

Solved: PowerBI Cumulative Total by Month within Year (Fin... - Microsoft Fabric Community

How to Calculate Cumulative Values with DAX - Microsoft Fabric Community

Solved: Cumulative sum of a column - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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