Forum Discussion
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:
| 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.
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
7 Replies
Hello POSPOS ,
would this work for you
Column = IF('Table'[Status] = "closed",DATEDIFF('Table'[Start Date],'Table'[End Date],DAY),0)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 💡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/
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 💡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
Anonymous - thank you for the response. This solution worked.
