Forum Discussion
Running Cumulative Total Weekly
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.
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?
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?
Hi,
I want those two columns to be added to the Date Table. Your relationship for now is just fine.
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.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.
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.
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.