Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
POSPOS
Post Partisan
2 years ago

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

POSPOS's avatar
POSPOS
Post Partisan
2 years ago

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/