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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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