March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
Hi All,
I have a requirement to calculate the difference between two dates. Please find sample data below:
Protocol | Start Date | Status | End Date |
A | 9/26/2022 | In-Progress | 11/20/2022 |
A | 9/26/2022 | Closed | 11/20/2022 |
B | 3/3/2022 | In-Progress | 4/12/2022 |
B | 3/3/2022 | Testing | 4/12/2022 |
B | 3/3/2022 | Closed | 4/12/2022 |
Expected Output:
Protocol | No.of days |
A | 55 |
B | 40 |
Wrong output that I am getting:
Protocol | No.of days |
A | 110 |
B | 120 |
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.
Solved! Go to 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
@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 💡
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
You are most welcome:-)
Hello @POSPOS ,
would this work for you
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 💡
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/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
89 | |
84 | |
70 | |
51 |
User | Count |
---|---|
206 | |
143 | |
97 | |
79 | |
68 |