DAX Cumulative Total over weekending dates

[SignorSoprano]

For the life of me, I cannot get this measure to work at the granularity of weeks. It works for Months, Quarters, and Years, but not weeks.

CumulativePlannedHours =
CALCULATE(
    SUM(pbiTaskPAE[TaskPlannedHours])
  , FILTER(
        ALLSELECTED(tblDates[Date])
      , ISONORAFTER(tblDates[Date], MAX(tblDates[Date]), DESC)
    )
)

I have tried many different versions of this formula using different filters and calculations but nothing works for weeks.

'tblDates' is my date table. It has many columns for weeks, months, quarters and year calculations. this 'pbiTaskPAE' table also has multiple fields. In this case, I am interested in [TaskPlannedHours], which has multiple records for a particular [TaskID]. Each record has a [EndDate] field which is a week-ending date. I want to cumulatively add each week's hours for each distinct [TaskID] so that I can indicate on which week, the planned hours for the task is equal to the actual hours for the task (i.e., on which week did the task complete?)

I also have a TimePeriodSelection table which has different period granularity selections (Week, Month, Quarter, Year), which I have as a slicer in my report. It's [Date] column is related to the tblDates[Date] column. The DAX for this TimeSelection is as follows:

TimePeriodSelection = 
  UNION(
     SELECTCOLUMNS(
       'tblDates'
       , "Date", [Date]
       , "Selection", "Week"
       , "Period", FORMAT([WeekEnding], "mm/dd/yy")
       , "Order By", [WeekInYear]
       , "Type ID", 0
     )

    , SELECTCOLUMNS(
        'tblDates'
        , "Date", [Date]
        , "Selection", "Month"
        , "Period", [MonthInCalendar]
        , "Order By", [MonthInYear]
        , "Type ID", 1
      )
    , SELECTCOLUMNS(
        'tblDates'
        , "Date", [Date]
        , "Selection", "Quarter"
        , "Period", [QuarterInCalendar]
        , "Order By", [QuarterInYear]
        , "Type ID", 2
      )
    , SELECTCOLUMNS(
        'tblDates'
        , "Date", [Date]
        , "Selection", "Year"
        , "Period", [ISOYear]
        , "Order By", [ISOYear]
        , "Type ID", 3
      )
  )

In my test visual, I have the following columns: TimePeriodSelection[Period], pbiTaskPAE[TaskPlannedHours], [CumulativePlannedHours].

Whenever I chose a TimePeriodSelection[Period] of "Month", "Quarter", or "Year", the [CumulativePlannedHours] measure is correct. But when I choose "Week", it just returns the pbiTaskPAE[TaskPlannedHours] value for the current record.

I have scoured the internet and used every cumulative sum measure I have found and none work for weeks in my case.

[Greg_Deckler · Accepted solution]

@SignorSoprano Try Better Running Total: Better Running Total - Microsoft Power BI Community

[SignorSoprano]

@Greg_Deckler I thought for sure I tried this formula before and it didn't work. I think the difference was that I didn't put the filtered table in a variable, but rather filtered it directly in SUMX.

Thanks.