topic Re: Help with Cumulative Max Calculation in Power BI in DAX Commands and Tips

Help with Cumulative Max Calculation in Power BI

CreativeEnergy — Mon, 28 Apr 2025 15:31:48 GMT

Hi all,

I’m working on a Power BI report where I have two values (Planning and Prognose) with fairly complex measures behind them. These values are spread across a series of weeks and companies. For each week and company combination, I want to calculate the maximum value between the two (using the MAX function), which works well so far. However, now I need to calculate the cumulative total based on the result of that MAX function, so it "grows" as the weeks progress.

For example, my data looks like this:

Company	Week	Planning	Prognosis	Max	Cumulative
Company 1	2024w01	20	30	30	30
Company 1	2024w02	40	20	40	70
Company 1	2024w03	40	50	50	120
Company 2	2024w01	30	20	30	30
Company 2	2024w02	60	30	60	90
Company 2	2024w03	40	50	50	140
Company 3	2024w01	90	20	90	90
Company 3	2024w02	60	50	60	150
Company 3	2024w03	20	70	70	220

The challenge I’m facing is how to calculate the Cumulative column. For each company, I want the cumulative sum to add up the maximum value for each week.

Here’s the approach I’ve tried:

I created a measure to get the max value for each week and company, which works as expected.
Then, I tried using a DAX formula to accumulate the max values, but I’m running into issues where the cumulative sum doesn’t update correctly across different companies.

Can anyone help me with the right DAX formula for this cumulative calculation?

Thanks in advance!

Re: Help with Cumulative Max Calculation in Power BI

Shravan133 — Tue, 29 Apr 2025 23:05:35 GMT

assuming:

You have a Date or Week dimension (which you should if you want proper time intelligence).
Your data table is called Data.
Week is a text column like "2024w01", so we need to sort it properly.

Step 1: Create a calculated column or measure for the row-wise maximum:

If you're working with measures:

MaxValue = MAX([Planning], [Prognosis])

If you're working in the data table and this can be a calculated column:

MaxValue = MAXX({[Planning], [Prognosis]}, [Value])

Step 2: Create a Cumulative Max measure:

To get a cumulative total of MaxValue per company, sorted by week:

Option A: If Week is a sortable column (like numeric week or proper date):

CumulativeMax =

VAR CurrentWeek = SELECTEDVALUE('Data'[Week])

RETURN

CALCULATE(

SUMX(

FILTER(

ALL('Data'),

'Data'[Company] = SELECTEDVALUE('Data'[Company]) &&

'Data'[Week] <= CurrentWeek

MAX([Planning], [Prognosis])

)

Option B: If Week is a text field like 2024w01 and not sorted properly:

You’ll need to create a sortable column (e.g., "202401" as integer) for the cumulative logic to work properly. Add a column:

SortWeek =

VAR Year = LEFT('Data'[Week], 4)

VAR WeekNum = RIGHT('Data'[Week], 2)

RETURN VALUE(Year & WeekNum)

Then sort your Week column by SortWeek.

Now update the CumulativeMax measure:

DAX

CopyEdit

CumulativeMax =

VAR CurrentSortWeek = SELECTEDVALUE('Data'[SortWeek])

RETURN

CALCULATE(

SUMX(

FILTER(

ALL('Data'),

'Data'[Company] = SELECTEDVALUE('Data'[Company]) &&

'Data'[SortWeek] <= CurrentSortWeek

MAX([Planning], [Prognosis])

)

Re: Help with Cumulative Max Calculation in Power BI

Anonymous — Wed, 30 Apr 2025 07:49:05 GMT

Hi CreativeEnergy,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

Re: Help with Cumulative Max Calculation in Power BI

Anonymous — Mon, 05 May 2025 05:24:31 GMT

Hi CreativeEnergy,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

Re: Help with Cumulative Max Calculation in Power BI

CreativeEnergy — Mon, 05 May 2025 05:47:57 GMT

Thank you for your response and excuse me for my late reply. I've tried this, but it seems that my Prognosis measure isn't working in the context unfortunately. Therefore, I have decided to rework the data model in a way that better fits this purpose. I assume it will work from there, but didn't have the time to fix that yet.

Re: Help with Cumulative Max Calculation in Power BI

Anonymous — Wed, 07 May 2025 06:53:08 GMT

Hi CreativeEnergy,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar