Running Cumulative Total Weekly

[Anonymous]

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('TimeKeeping_Actual Time'[[Time]] Date])))

But what I want is a running cumulative time total by week, looks like showing in red under column "Goal":

Can anybody help me to solve this problem?

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file.

[Anonymous]

Hi Ashish,

Thanks very much for the help.

See whether you can open this link

https://latzero-my.sharepoint.com/:x:/g/personal/michelle_li_asbuilt_co_nz/EUwgdkCTsmJGiv4IHvdICB8Bg...

My raw data for this excel is from Query, which is from a folder and automatic refresh once data been dropped into the folder.
Do I need to share the folder to you too?

[Ashish_Mathur]

Hi,

In the Date Table, there should be a way to get the Week and Weekday columns (which are presently in your Timekeeping Table.  If that can be done, the we will drag those two columns from the Date Table to the visual and write a simple measure to get your desired result.

Can you somehow do that?

[Anonymous]

Yes, I can add a new column in Power Query under the "TimeKeeping_ActualTime", and extract Year and Week and WeekDay from this Date colunn. So all date information are all under "TimeKeeping_ActualTime" Table, and there is no need to have this Date Table.  And all those newly added calculated columns can be added as slicer.

Because my current Cumulative measurement does not use anything from Date Table, and I plan to delete this table.

Or, I can do the Date as a new table/sheet and connect to the existing PowerPivot Table, and build ulti-to-one relationships between "TimeKeeping_ActualTime" and "Date" tables.

Which way you prefer me to do?
Are there any difference?

[Ashish_Mathur]

Hi,

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

[Anonymous]

And one more question:

When I add Date from Query, the query name is still "Date".

But when I add it into Pivot Model, the table name is now "Date 1".

When I tried to edit and rename it, the dialogue box says I should edit it under query rather than in Pivot.

But I don't know how to in Query.

Would you please help on this one too?

Many tks.

[Anonymous]

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]

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]

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.

[Ashish_Mathur]

Hi,

File attached here.

[Anonymous]

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]

Hi,

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

[Anonymous]

Daily Actual VS Est _Ashish.xlsx

[Ashish_Mathur · Accepted solution]

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]

Thanks very much, Ashish.

The function works fine. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

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]

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.

[Anonymous]

Cool, I will do it right now.

When I am done, I will share the link of the file again.

Again, tks very much for the help.