Calculate date difference without aggregating using DAX

[POSPOS]

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.

[Idrissshatila]

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

[Anonymous · 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 

[POSPOS]

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

[Anonymous]

You are most welcome:-)

[Idrissshatila]

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 💡

[POSPOS]

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

[POSPOS]

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