Forum Discussion
Cumulative Sum based on Period End
Hi All,
I am trying to calculate the cumulative sum of an amount based on some conditional logic.
What I am trying to do is calculate the WIP_Taget_AMT by Per_End_Date where the WIP has not been billed therefore where WIP_CLEAR_DATE is either NULL or Greater than the Per_End_Date.
I am doing the following:
Hi Jazz_MT ,
Is the excepted result you need?
Running Total MEASURE = CALCULATE ( SUM ( Wip_Detail[WIP_Target_AMT] ), FILTER ( ALL(Wip_Detail), Wip_Detail[Per_End_Date] <= MAX(Wip_Detail[Per_End_Date]) && (Wip_Detail[WIP_CLEAR_DATE] >= MAX(Wip_Detail[Per_End_Date]) ) ),VALUES(Wip_Detail[Per_End_Date]))
7 Replies
Hi All,
Please find the table below:
What I need to do is get the Running total of WIP_Target_AMT grouped by Per_End_Date where the WIP_CLEAR_DATE is greater than Per_End_Date or WIP_Clear_Date is NULL.
Thanks
------
JobEntity WIP_ID PostDate Per_End_Date WIP_Target_AMT WIP_CLEAR_DATE JobChargeCd isBilled 1 4070921 20/06/2019 30/06/2019 560 NULL EB NULL 1 4070735 20/06/2019 30/06/2019 145 30/06/2019 EB Y 1 4070786 20/06/2019 30/06/2019 110 NULL EB NULL 1 4070827 21/06/2019 30/06/2019 187.5 30/06/2019 EB Y 1 4069454 18/06/2019 30/06/2019 362.5 30/06/2019 EB Y 1 4070617 19/06/2019 30/06/2019 125 30/06/2019 EB Y 1 4070056 18/06/2019 30/06/2019 175 30/06/2019 EB Y 1 4070510 18/06/2019 30/06/2019 142.5 NULL EB NULL 1 4070075 18/06/2019 30/06/2019 49 30/06/2019 EB Y 1 4070833 18/06/2019 30/06/2019 125 NULL EB NULL 1 4069689 17/06/2019 30/06/2019 237.5 NULL EB NULL 1 4070998 18/06/2019 30/06/2019 -51 NULL EB NULL 1 4070326 19/06/2019 30/06/2019 14 30/06/2019 EB Y 1 4070367 19/06/2019 30/06/2019 25 30/06/2019 EB Y 1 4070847 20/06/2019 30/06/2019 125 NULL EB NULL 1 4070999 18/06/2019 30/06/2019 51 NULL EB NULL 1 4070503 18/06/2019 30/06/2019 560 NULL EB NULL 1 4069456 17/06/2019 30/06/2019 25.5 30/06/2019 EB Y 1 4070793 18/06/2019 30/06/2019 110 NULL EB NULL 1 4070613 19/06/2019 30/06/2019 37.5 30/06/2019 EB Y 1 4070838 18/06/2019 30/06/2019 62.5 NULL EB NULL 1 4070325 18/06/2019 30/06/2019 140 30/06/2019 EB Y 1 4052359 10/06/2019 15/06/2019 58 NULL EB NULL 1 4052155 04/06/2019 15/06/2019 100 NULL EB NULL 1 4051272 12/06/2019 15/06/2019 33 30/06/2019 EB Y 1 4051489 05/06/2019 15/06/2019 12.5 30/06/2019 EB Y 1 4051491 10/06/2019 15/06/2019 37.5 30/06/2019 EB Y 1 4051137 05/06/2019 15/06/2019 17 30/06/2019 EB Y 1 4051493 13/06/2019 15/06/2019 37.5 30/06/2019 EB Y 1 4051492 12/06/2019 15/06/2019 62.5 30/06/2019 EB Y 1 4051490 06/06/2019 15/06/2019 12.5 30/06/2019 EB Y 1 4052166 13/06/2019 15/06/2019 62.5 NULL EB NULL 1 4052006 03/06/2019 15/06/2019 12.5 NULL EB NULL 1 4052133 11/06/2019 15/06/2019 50 NULL EB NULL 1 4051924 12/06/2019 15/06/2019 50 NULL EB NULL 1 4052147 06/06/2019 15/06/2019 62.5 NULL EB NULL 1 4052741 05/06/2019 15/06/2019 50 NULL EB NULL 1 4052283 04/06/2019 15/06/2019 340 NULL EB NULL 1 4052326 14/06/2019 15/06/2019 72.5 NULL EB NULL 1 4048691 04/06/2019 15/06/2019 12.5 NULL EB NULL Hi,
Share the expected result.
Hi Jazz_MT ,
How about this one?
Running Total MEASURE = CALCULATE ( SUM ( Wip_Detail[WIP_Target_AMT] ), FILTER ( ALL(Wip_Detail), Wip_Detail[Per_End_Date] <= MAX(Wip_Detail[Per_End_Date]) && (Wip_Detail[WIP_CLEAR_DATE] <= MAX(Wip_Detail[Per_End_Date]) ) ))
It is on the right track but not fully there :( Posted below re a sample of the data.
Thanks for the help :)
Hi Jazz_MT ,
Is the excepted result you need?
Running Total MEASURE = CALCULATE ( SUM ( Wip_Detail[WIP_Target_AMT] ), FILTER ( ALL(Wip_Detail), Wip_Detail[Per_End_Date] <= MAX(Wip_Detail[Per_End_Date]) && (Wip_Detail[WIP_CLEAR_DATE] >= MAX(Wip_Detail[Per_End_Date]) ) ),VALUES(Wip_Detail[Per_End_Date]))
