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

 

Morning Ashish, 

Columns been added

So is the relationship between two Tables

Here is the new link: Daily Actual VS Est.xlsx 

How should I edit the "Cumulative" Measurement to make it running weekly.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

In the Query Editor, ensure that the Weekday column is a numeric column.  In yours, it is a text column.  Then drag my measure (Measure 1) in the Pivot on the "Ashish solution" worksheet.  Download my file from here.

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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

    Thanks Ashish

    I edit the Weekday format to whole number.

    The problem is the shared link of your file, I cannot open it.

    How can I solve this problem?

    I tried to log into Microsoft 365 under my company account, but it says the account doesn't exist.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Sorry to keep bothering

    Still unsolved...

     

    I edit the 'Date1'[Weekdays] format to whole number under query then reload to Pivot

    But the measure 1 still shows Error

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Share the link from where i can download your file with the coumn being  a whole number.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks very much, Ashish.

    The function works fine. :cathappy:

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    You are welcome.

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