DAX for Cumulative Total

[Anonymous]

Hello,

Need help on DAX function.

 We are working on one PBI report where we require a DAX function which would update the cumulative value from column. These columns are from same tables and each column are fetched using the measures. We have used the matrix table for each column to sum the data, please find the below requirements and current output we received while using the DAX function.

Requirement:

1) When the days to departure column has data, value column is blank then Cumulative value should be shown as 0.

2) When the days to departure column is Blank & Value column is blank then Cumulative value should be 0.

3) When the days to departure column has data & Value column is blank in the middle then cumulative value should be the above column value. E:G days to departure(353,350 349, 348) 

3) Total number of Cumulative values should be Sum of Value.

Dax we used:

Value  = CALCULATE([NPR_SUM_REVENUE_COMPONENT_LY],QRG0_CHANGE_CALENDAR_DIM[PERIOD] ="LY",TRG0_FWD_FACT[HELD_FLAG]="H",QRG0_DEPU_CALENDAR_DIM[F_DEPDATE_LY_HELD]=1,QRG0_CHANGE_CALENDAR_DIM[F_RECCHGDATE_LY_HELD]=1)

Cumulative_Value =

VAR CurrentDay = MAX(TRG0_FWD_FACT[DAYS_TO_DEPARTURE_POSITIVE])

VAR Cumulative = CALCULATE(IF(ISBLANK([NPR_HELD_LY]) && ISBLANK(CurrentDay),0,[NPR_HELD_LY]),FILTER(ALLSELECTED(TRG0_FWD_FACT[DAYS_TO_DEPARTURE_POSITIVE]),TRG0_FWD_FACT[DAYS_TO_DEPARTURE_POSITIVE]>=MAX(TRG0_FWD_FACT[DAYS_TO_DEPARTURE_POSITIVE])))

RETURN

IF(ISBLANK([NPR_HELD_LY]),Cumulative,Cumulative)

Current Output
Class	A		B
DaysTodeparture	Value	Cumulative Value	Value	Cumulative Value
359		815		396
		815		396
358	90	90	78	78
357	56	146	80	158
356	345	491	28	186
355	234	725		396
354	43	768	89	275
353		815		396
352	-98	670		396
351	143	813	31	306
350		813		396
349		813		396
348	2	815	90	396
Total	815	0	396	0

Expected Output
Class	A		B
DaysTodeparture	Value	Cumulative Value	Value	Cumulative Value
359		0		0
		0		0
358	90	90	78	78
357	56	146	80	158
356	345	491	28	186
355	234	725		186
354	43	768	89	275
353		768		275
352	-98	670		275
351	143	813	31	306
350		813		306
349		813		306
348	2	815	90	396
Total	815	815	396	396

Screenshot: 

[AlexisOlson · Accepted solution]

I would try a WINDOW function for cumulative sums.

Something like this:

Cumulative_Value =
CALCULATE (
    [NPR_HELD_LY],
    WINDOW (
        1, ABS, /* First row */
        0, REL, /* Current row */
        ORDERBY ( TRG0_FWD_FACT[DAYS_TO_DEPARTURE_POSITIVE], DESC )
    )
)

[v-tejrama]

Hi @Anonymous ,

Thanks for reaching out to the Microsoft fabric community forum.

The Super User solution provided a solid starting point by outlining the concept and using SUMX with a filtered table, but after testing it in Power BI with an actual dataset and building a .pbix file, I found it didn’t fully address the issue.

Specifically, the cumulative total didn’t reset per group (Class), and there were issues when DaysToDeparture needed to be sorted descending an essential requirement for this type of rolling aggregation. It also struggled with blanks in the data.

To resolve these challenges, I developed a robust solution in Power BI that accurately handles cumulative totals per group, supports descending order for DaysToDeparture, manages blanks effectively, and avoids circular dependencies to keep the model streamlined.

Here is the DAX: 

Cumulative_Value =
VAR SelectedClass = MAX(DepartureData[Class])
VAR SelectedDay = MAX(DepartureData[DaysToDeparture])
RETURN
CALCULATE(
    SUM(DepartureData[Value]),
    FILTER(
        ALL(DepartureData),
        DepartureData[Class] = SelectedClass &&
        DepartureData[DaysToDeparture] >= SelectedDay
    )
)

By simply sorting DaysToDeparture descending in the matrix visual, the cumulative totals now roll correctly from highest to lowest within each class no need for helper columns, RANKX, or Calculation Groups.

I tested this in a sample .pbix file, and it’s working perfectly as expected.

Please find the attached .pbix file for your reference.

If the response has addressed your query, please "Accept it as a solution" and give a "Kudos" so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[Anonymous · Accepted solution]

Thank you @AlexisOlson its working fine.

[AlexisOlson · Accepted solution]

I would try a WINDOW function for cumulative sums.

Something like this:

Cumulative_Value =
CALCULATE (
    [NPR_HELD_LY],
    WINDOW (
        1, ABS, /* First row */
        0, REL, /* Current row */
        ORDERBY ( TRG0_FWD_FACT[DAYS_TO_DEPARTURE_POSITIVE], DESC )
    )
)