Calculate Remaining Efforts based on the Completed Efforts on previous dates

[faisalek]

     I would like to know how to calculate  the remaining efforts based on the completed efforts on previous dates. I have a table like below where it has a Key (ID) , Completed Date, Total Effort for that Key (ID) and completed efforts on the given date.

ID	Completed on	Total Efforts	Completed
AG-107	22 November 2019	49	17
AG-107	20 December 2019	49	6
AG-107	17 January 2020	49	2
AG-107	14 February 2020	49	3
AG-107	28 February 2020	49	7
AG-107	13 March 2020	49	9
AG-107	27 March 2020	49	5
AG-115	19 June 2020	29	8
AG-115	20 December 2019	29	2
AG-115	17 January 2020	29	4
AG-115	06 December 2019	29	6
AG-115	31 January 2020	29	7
AG-115	13 March 2020	29	2

Now, I want to calulcate the column called "Remaining efforts" which is the effort remaining to complete on that date. ( Total Efforts - Efforts completed till that date) like below.

ID	Completed on	Total Efforts	Completed	Remaining Efforts
AG-107	22 November 2019	49	17	32
AG-107	20 December 2019	49	6	26
AG-107	17 January 2020	49	2	24
AG-107	14 February 2020	49	3	21
AG-107	28 February 2020	49	7	14
AG-107	13 March 2020	49	9	5
AG-107	27 March 2020	49	5	0
AG-115	19 June 2020	29	8	21
AG-115	20 December 2019	29	2	19
AG-115	17 January 2020	29	4	15
AG-115	06 December 2019	29	6	9
AG-115	31 January 2020	29	7	2
AG-115	13 March 2020	29	2	0

I tried like below,

MAX('Effort Breakdown'[Total Efforts]) -SUMX(FILTER(All( 'Effort Breakdown'[Completed on]), 'Effort Breakdown'[Completed on] <= EARLIER('Effort Breakdown'[Completed on])) , [Total Completed] )

and 

Total Completed is a Measure with formula If(ISBLANK(SUM( 'Effort Breakdown'[Total Completed] )),0, SUM('Effort Breakdown'[Total Completed] ))

But remaining values are not coming as expected.  Could you please help me how can I achieve this?

[ryan_mayu]

@faisalek 

Plesae try to create an index column , then create a new column

Column = 'Table'[Total Efforts]-SUMX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Completed])

 

[amitchandak]

@faisalek , Try a new column like

[Total Efforts]- sumx(filter(Table, [ID] =earlier([ID]) && [Completed on] <=earlier([Completed on]) ),[Completed])

New measure

sumx(Summarize(Table[ID],Table[Completed on],"_1", max([Total Efforts]) - sumx(filter(allselected(Table), [ID] =max([ID]) && [Completed on] <=max([Completed on]) ),[Completed])),[_1])

[nandukrishnavs · Accepted solution]

@faisalek 

If you are looking for a calculated column, try below DAX

Remaining Effort =
VAR __TotalEffort = [Total Efforts]
VAR __CompletedEffort =
    SUMX (
        FILTER (
            'Effort Breakdown',
            'Effort Breakdown'[ID] = EARLIER ( 'Effort Breakdown'[ID] )
                && 'Effort Breakdown'[Completed on] <= EARLIER ( 'Effort Breakdown'[Completed on] )
        ),
        'Effort Breakdown'[Completed]
    )
VAR __Remaining = __TotalEffort - __CompletedEffort
RETURN
    __Remaining

[faisalek]

Great, That works.. Thank you for the replay and effort.