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