Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
SignorSoprano
Helper I
Helper I

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.

 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.