Forum Discussion

Jazz_MT's avatar
Jazz_MT
Frequent Visitor
7 years ago
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 ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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]))

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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])
)
))
Jazz_MT's avatar
Jazz_MT
Frequent Visitor
7 years ago

It is on the right track but not fully there :( Posted below re a sample of the data. 

Thanks for the help :) 

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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]))