Cumulative Total of DAX Measure without Date Filters

Question

I have the following measure in Power BI:

_turnover_chart = 
VAR applNos = 
 CALCULATETABLE(
    DISTINCT(combined_apps[appl_appl_no]), 
    combined_apps[table_type] = "TRAN"
    )

RETURN

CALCULATE(
  SUM(combined_apps[tran_amount]),
  REMOVEFILTERS('calendar'),
  combined_apps[appl_appl_no] IN applNos,
  combined_apps[table_type] = "TRAN"
 )

The measure is working fine, now I need to create a new running total measure that will sum for each date in my 'calendar'[cal_posted_date] table. Below screenshot is how the table looks if I have filtered for Oct 2023:

I have tried the following:

VAR MaxDate = MAX('calendar'[cal_posted_date])
VAR Result = 
CALCULATE(
    _measures[_turnover_chart],
    'calendar'[cal_posted_date] <= MaxDate,
    ALL('calendar'[cal_posted_date])
)
RETURN Result

However this does not work. I feel I am going wrong with the date filters because the _turnover_chart measure ignores the calendar filters. For clarity, the original measure is first considering all application numbers that came in on the cal_posted_date, and then finding the sum of all tran_amount for those relevant application numbers.

Please could I get some assistance with this.

Answer 1

It seems that the problem with what you did so far is how you deal with the date context and the calculation of the turnover_chart measure within the Result variable.

When you use CALCULATE to change the filter context, you need to make sure that the date filter is applied properly and so that the total which is accumulated goes up to the MostDate.

On the other hand, you are using ALL('calendar'[cal_posted_date]) so here you are resetting the filter context in a way that is not in line with your goal of calculating a running total.

RunningTotal_turnover_chart = 
VAR MaxDate = MAX('calendar'[cal_posted_date])
RETURN
SUMX(
    FILTER(
        ALL('calendar'),
        'calendar'[cal_posted_date] <= MaxDate
    ),
    CALCULATE(
        [_turnover_chart],
        'calendar'[cal_posted_date] <= MaxDate
    )
)