Forum Discussion

SignorSoprano's avatar
3 years ago
Solved

DAX Cumulative Total over weekending dates

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.