Cumulative Sum based on Period End

[Jazz_MT]

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: 

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

)

))

However its not working... Can anybody help out ? 

[v-frfei-msft]

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]

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 · Accepted solution]

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

[Anonymous]

Can you post sample data of your WIP_Detail table?

[Jazz_MT]

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

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share the expected result.