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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DicksonJeff
New Member

Trying to calculate sum on a metric (DateDiff)

I have a metric calculating the difference between the first and last date in a series of date changes. 

 

Days Close Date Delta = DATEDIFF([First Close Date],[Last Close Date],DAY)
First Close Date is the min of TableName.CloseDate likewise the last close date is the max tablename.closedate table and field. 

My objective is to next create another metric that totals all of the differences that are found. Below is a table showing records and the date difference (delta) if there is one. Currently my formula to total the differences comes to 5 which is the count. I would like to see a total of 572 as an example. The logic seems simple but not being able to use a metric in a sum function is throwing me a wrench. 
 
DicksonJeff_0-1624913925873.png

 

Thanks in advance

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

You can use a SUMX like this:

DeltaTotal =
SUMX ( VALUES ( TableName[Column] ), [Days Close Date Delta] )

Where [Column] is whatever column is the lowest level of granularity in your visual.

View solution in original post

Thank you! i had been trying something similar but basing it off of the date field and not the id field. that fixed my issue!

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

You can use a SUMX like this:

DeltaTotal =
SUMX ( VALUES ( TableName[Column] ), [Days Close Date Delta] )

Where [Column] is whatever column is the lowest level of granularity in your visual.

Thank you! i had been trying something similar but basing it off of the date field and not the id field. that fixed my issue!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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