Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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])
Proud to be a Super User!
@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])
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
Great, That works.. Thank you for the replay and effort.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |