DAX Expression to SUM values on and before a date based on values for that date

Question

I have the following tables in power BI: calendar and combined_apps. calendar is joined to combined_apps via 'calendar'[cal_posted_date] to combined_apps[date_join] in a one to many relationship.

I am trying to calculate the SUM(combined_apps[tran_amount]).

I have a slicer on my page that uses 'calendar'[cal_posted_date]. Lets say I have selected the date of 2023-10-18. I want to calculate the sum of tran_amount for this date, and all dates on and before, but only where the combined_apps[appl_appl_no] is equal to the appl_appl_no in the selected date.

So for example, if on the 2023-10-18 the sum equals $1000, and the appl_appl_no's for this date were 20055, 20035, and 20038, I want the the sum of these numbers for this date, and for all the dates before.

I am using the following formula:

Purchases = 
VAR SelectedDates = VALUES('calendar'[cal_posted_date])
RETURN
CALCULATE(
    SUM(combined_apps[tran_amount]),
    combined_apps[tran_code] IN {"35", "37"},
    combined_apps[tran_effective_date] <= MAXX(SelectedDates, 'calendar'[cal_posted_date]),
    combined_apps[appl_appl_no] IN
        CALCULATETABLE(
            VALUES(combined_apps[appl_appl_no]),
            combined_apps[tran_effective_date] IN SelectedDates
        )
)

The problem is, it is still calculating for only the selected date, and not the dates before. I realize this is because even though I'm asking it to calculate for the previous dates, because the slicer is set at 2023-10-18, it still only calculates for that date. How can I amend my DAX expression to cater for what I want?

Answer 1

You will need to remove the "filter context" from the date slicer, either by ALL or REMOVEFILTERS. Try:

Purchases = 
  var maxDate = MAX('calendar'[cal_posted_date])
  var applNos = DISTINCT(combined_apps[appl_appl_no])

  return
    CALCULATE(
      SUM(combined_apps[tran_amount]),
      REMOVEFILTERS('calendar'),
      combined_apps[tran_code] IN {"35", "37"},
      combined_apps[tran_effective_date] <= maxDate,
      combined_apps[appl_appl_no] IN applNos
  )