Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Sum of TCV by Project and Max Date

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

 

Week StartTotalProject NameStatusModifiedDateTCV
03/20/2021 0:002AApproved04/11/2021 9:3015
03/20/2021 0:001AApproved04/11/2021 9:3015
03/27/2021 0:002BApproved04/11/2021 9:30300
03/27/2021 0:003BApproved04/11/2021 9:30300
03/27/2021 0:003BApproved04/11/2021 9:30300
03/27/2021 0:002BApproved04/21/2021 7:41230
03/20/2021 0:001CApproved04/11/2021 9:3025
03/20/2021 0:001CApproved04/11/2021 9:3025
03/20/2021 0:001CApproved04/11/2021 9:3025
03/27/2021 0:002CApproved04/11/2021 9:3025
03/27/2021 0:004CApproved04/11/2021 9:3025
03/27/2021 0:003CApproved04/21/2021 7:417

 

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.
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

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

 

Picture5.png

 

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

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


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


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

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

 

Picture5.png

 

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

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


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


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

Working as expected, thanks for the help

amitchandak
Super User
Super User

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

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.