Forum Discussion
Solved
Weekly Movement calculation
Hi,
I have a table of data which capture "Cumulative Expended Manhours" on every week. I need a column/measure that calculates the weekly value for each activty code on that week. example table as below. I have attached the sample Power Bi data as well.
https://www.dropbox.com/s/5xwfcnq9is0ro6x/test.pbix?dl=0
| Activity Code | Week End Date | Cumulative Expended Manhours | Period Expended Manhours |
| test | 15-Oct-2021 | 15.00 | 15.00 |
| test | 22-Oct-2021 | 30.00 | 15.00 |
| test | 29-Oct-2021 | 45.00 | 15.00 |
| test | 05-Nov-2021 | 60.00 | 15.00 |
| test | 12-Nov-2021 | 75.00 | 15.00 |
| test | 19-Nov-2021 | 90.00 | 15.00 |
| test | 26-Nov-2021 | 105.00 | 15.00 |
| test | 03-Dec-2021 | 162.50 | 57.50 |
| test | 10-Dec-2021 | 178.50 | 16.00 |
| test | 17-Dec-2021 | 211.00 | 32.50 |
| test | 24-Dec-2021 | 211.00 | 0 |
| test | 31-Dec-2021 | 211.00 | 0.00 |
| test | 07-Jan-2022 | 223.00 | 12.00 |
| test | 14-Jan-2022 | 238.00 | 15.00 |
| test | 21-Jan-2022 | 248.00 | 10.00 |
| test | 28-Jan-2022 | 256.00 | 8.00 |
| test | 04-Feb-2022 | 267.00 | 11.00 |
| test2 | 19-Nov-2021 | 0.00 | 0 |
| test2 | 26-Nov-2021 | 0.00 | 0 |
| test2 | 03-Dec-2021 | 0.00 | 0 |
| test2 | 10-Dec-2021 | 0.00 | 0 |
| test2 | 17-Dec-2021 | 0.00 | 0 |
| test2 | 24-Dec-2021 | 0.00 | 0 |
| test2 | 31-Dec-2021 | 0.00 | 0 |
| test2 | 07-Jan-2022 | 16.00 | 16 |
| test2 | 14-Jan-2022 | 32.00 | 16 |
| test2 | 21-Jan-2022 | 56.00 | 24.00 |
| test2 | 28-Jan-2022 | 56.00 | 0 |
| test2 | 04-Feb-2022 | 77.00 | 21.00 |
2 Replies
Hi,
This calculated column formula works
=Data[Cumulative Expended Manhours]-LOOKUPVALUE([Cumulative Expended Manhours],Data[Activity Code],Data[Activity Code],Data[Week End Date],CALCULATE(MAX(Data[Week End Date]),FILTER(Data,Data[Activity Code]=EARLIER(Data[Activity Code])&&Data[Week End Date]<EARLIER(Data[Week End Date]))))Hope this helps.
