Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running Cumulative Total Weekly

I have a cumulative total in PowerPivot working like following:   Cumulative Time:=CALCULATE([SUM Actual Time],FILTER(ALL('TimeKeeping_Actual Time'),'TimeKeeping_Actual Time'[[Time]] Date] <= MAX('...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    This measure works.  Download the file from here.

     

    Measure1 = if(ISBLANK([Actual Time]),BLANK(),CALCULATE([Actual Time],DATESBETWEEN('Date 1'[Date],CALCULATE(MIN('Date 1'[Date]),ALL('Date 1'[asBUILT Weekday])),MAX('Date 1'[Date]))))

     

    Hope this helps.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Ashish,

Just the last piece of function, I cannot figure out how it works (what is the logic)

 

CALCULATE(MIN('Date 1'[Date]),ALL('Date 1'[asBUILT Weekday]))

 

MIN('Date 1'[Date]),ALL('Date 1'[asBUILT Weekday])

 

This CALCULATE() is for the second value <start date> of  DATESBETWEEN(<dates>,<start_date>,<end_date>), right?

 

This is the part I don’t understand. How this nested function work out cumulative time by week. Would you please explain a bit to help me understand?

How you make the cumulative time run by week?

 

Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    CALCULATE(MIN('Date 1'[Date]),ALL('Date 1'[asBUILT Weekday])) will tell you the minimum date in every week when all weekdays of the Week are considered i.e. the first date of every week.