topic Re: Sum of TCV by Project and Max Date in DAX Commands and Tips

Sum of TCV by Project and Max Date

Anonymous — Fri, 30 Apr 2021 08:41:07 GMT

I need to Sum TCVs based on MAX Modified Date and group by Project Name

Week Start	Total	Project Name	Status	ModifiedDate	TCV
03/20/2021 0:00	2	A	Approved	04/11/2021 9:30	15
03/20/2021 0:00	1	A	Approved	04/11/2021 9:30	15
03/27/2021 0:00	2	B	Approved	04/11/2021 9:30	300
03/27/2021 0:00	3	B	Approved	04/11/2021 9:30	300
03/27/2021 0:00	3	B	Approved	04/11/2021 9:30	300
03/27/2021 0:00	2	B	Approved	04/21/2021 7:41	230
03/20/2021 0:00	1	C	Approved	04/11/2021 9:30	25
03/20/2021 0:00	1	C	Approved	04/11/2021 9:30	25
03/20/2021 0:00	1	C	Approved	04/11/2021 9:30	25
03/27/2021 0:00	2	C	Approved	04/11/2021 9:30	25
03/27/2021 0:00	4	C	Approved	04/11/2021 9:30	25
03/27/2021 0:00	3	C	Approved	04/21/2021 7:41	7

So for the Week of 03/27/2021, MAX Modified Date of Project B is 14/21 so TCV is 230 and the MAX Modified Date of Project C is 04/21 so TCV is 7.

Total TCV - 230 + 7 = 237
Similarly, for the Week of 03/20/2021, MAX Modified Date of Project A is 04/11 so TCV is 15, and the MAX Modified Date of Project C is 04/11 so TCV is 25.

Total TCV - 15 + 25 = 40

And if I select for both weeks 03/27/2021 and 03/20/2021, MAX Modified Date of Project A is 04/11 so TCV is 15,

and the MAX Modified Date of Project B is 04/21 so TCV is 230,

and the MAX Modified Date of Project C is 04/21 so TCV is 7.

Total TCV - 15 + 230 + 7 = 252

I tried 2 measures but both not working.

Measure1 = CALCULATE(

SUM(TimeEntries[TCV]), FILTER(TimeEntries, TimeEntries[ModifiedDate] = Max(TimeEntries[ModifiedDate])))

Measure2 =

SUMX(

SUMMARIZE(TimeEntries, TimeEntries[Project Name], TimeEntries[TCV], "MAX SUM", MAX(TimeEntries[ModifiedDate])), TimeEntries[TCV])

Please help me with this.

Re: Sum of TCV by Project and Max Date

amitchandak — Fri, 30 Apr 2021 08:51:01 GMT

Anonymous , Try a measure like

Measure =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[NUM] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( MAX ('Table'[Status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[NUM] = __date )

Re: Sum of TCV by Project and Max Date

Anonymous — Fri, 30 Apr 2021 10:22:30 GMT

How do get the Sum of TCV?

I tried but the value is incorrect

Measure2 =
VAR _id = MAX ('TimeEntries'[ID] )
VAR _date = CALCULATE ( MAX('TimeEntries'[ModifiedDate] ), ALLSELECTED ('TimeEntries' ), 'TimeEntries'[ID] = _id )
return CALCULATE(SUM(TimeEntries[TCV]), VALUES ('TimeEntries'[ID] ),'TimeEntries'[ID] = _id,'TimeEntries'[ModifiedDate] = _date )

it's returning me 230 but supposed to be 252 for 2 weeks

Re: Sum of TCV by Project and Max Date

Jihwan_Kim — Fri, 30 Apr 2021 10:45:46 GMT

Hi, Anonymous

Please check the below picture and the sample pbix file's link down below.

TCV Total =

VAR groupbymaxmodifieddate =

SUMMARIZE (

GROUPBY (

'Table',

'Table'[Project Name],

"@maxdate", MAXX ( CURRENTGROUP (), 'Table'[ModifiedDate] )

'Table'[Project Name],

[@maxdate],

"@tcv", LASTNONBLANKVALUE ( 'Table'[ModifiedDate], MAX ( 'Table'[TCV] ) )

)

RETURN

SUMX ( groupbymaxmodifieddate, [@tcv] )

https://www.dropbox.com/s/31nnf7gvhlgv25x/abhishekmor.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Sum of TCV by Project and Max Date

Anonymous — Mon, 10 May 2021 08:08:53 GMT

Working as expected, thanks for the help