Forum Discussion
Solved
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 ...
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]))
Can you post sample data of your WIP_Detail table?
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.
RunningTotal = SUMX( FILTER( ALL(Wip_Details), Wip_Details[PostDate]<=MAX(Wip_Details[Per_End_Date]) && OR( ISBLANK(Wip_Details[WIP_CLEAR_DATE]), Wip_Details[WIP_CLEAR_DATE]>MAX(Wip_Details[Per_End_Date]) ) ) ,Wip_Details[WIP_Target_AMT] )
