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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

I want those two columns to be added to the Date Table.  Your relationship for now is just fine.

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.

     

     

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