Weekly Movement calculation

[amirghaderi]

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

[CNENFRNL · Accepted solution]

[Ashish_Mathur]

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.