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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
POSPOS
Post Patron
Post Patron

Calculate date difference without aggregating using DAX

Hi All,

I have a requirement to calculate the difference between two dates. Please find sample data below:

 

ProtocolStart DateStatusEnd Date
A9/26/2022In-Progress11/20/2022
A9/26/2022Closed11/20/2022
B3/3/2022In-Progress4/12/2022
B3/3/2022Testing4/12/2022
B3/3/2022Closed4/12/2022

 

Expected Output:

ProtocolNo.of days
A55
B40


Wrong output that I am getting:

ProtocolNo.of days
A110
B120



Issue is that, the no. of days are been aggregared.


Eg:  No. of days between 9/26/2022 and 11/20/2022 for protocol A is 55. But since there are two satutses for a single protocol, the days are getting aggregated resulting in 110.

I have created a new column with below dax.

 

 

newcolumn=datediff([startdate],[enddate],day)

 

 

Removing the 'Don't Summarize' option did not work as I will be using this to calcualte the Average.
Expected Average is (55+40)/2=47.5 but I get (110+120)/2=115

 

Could someone please advise on how to achieve this using DAX.

Thank you.

1 ACCEPTED SOLUTION

Simplest dax can be as follows.

SUMX(SUMMARIZE('Table','Table'[protocol],'Table'[start],'Table'[end],"datediff",DATEDIFF('Table'[start],'Table'[end],DAY)),[datediff]).

 

This will give you was u required.

Summarize is good approach to avoid certain columns which creates aggregation or double dip on values 

View solution in original post

7 REPLIES 7
Idrissshatila
Super User
Super User

@POSPOS , then replace the closed status in the measure into open, thus you'll take one row.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Simplest dax can be as follows.

SUMX(SUMMARIZE('Table','Table'[protocol],'Table'[start],'Table'[end],"datediff",DATEDIFF('Table'[start],'Table'[end],DAY)),[datediff]).

 

This will give you was u required.

Summarize is good approach to avoid certain columns which creates aggregation or double dip on values 

@Rameez - thank you for the response. This solution worked.

Rameez
Frequent Visitor

You are most welcome:-)

Idrissshatila
Super User
Super User

Hello @POSPOS ,

 

would this work for you

Column = IF('Table'[Status] = "closed",DATEDIFF('Table'[Start Date],'Table'[End Date],DAY),0)
 
Idrissshatila_0-1695931628336.png

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




@Idrissshatila  - Not all are having the same list of statuses. Some of them may still be open.

@Idrissshatila : Restricting by one statuses may not work for the data that we have as we have some of the protocols which are directly approved and hence they just have one status called 'approved' without open/close/in-progress. 
Each of them may have a different criteria of statuses/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.