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

Muska_Sukhija — Thu, 19 Dec 2024 17:11:57 GMT

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!

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

Anonymous — Mon, 30 Dec 2024 08:03:54 GMT

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.

topic Re: Help with calculating cumulative change and % in Power BI with multiple months selected in DAX Commands and Tips

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

Problem Statement:

Question:

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