DAX Cumulative Total over weekending dates

SignorSoprano — Tue, 18 Apr 2023 19:35:53 GMT

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.

Re: DAX Cumulative Total over weekending dates

Greg_Deckler — Tue, 18 Apr 2023 21:15:14 GMT

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

Re: DAX Cumulative Total over weekending dates

SignorSoprano — Wed, 19 Apr 2023 16:35:49 GMT

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.

topic Re: DAX Cumulative Total over weekending dates in DAX Commands and Tips

DAX Cumulative Total over weekending dates

Re: DAX Cumulative Total over weekending dates

Re: DAX Cumulative Total over weekending dates