Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date Difference with Filters

Project No. Tasks in the Projects Start Date End Date AB10001 XXYYZZ12 8/24/2022 9/5/2022 AB10001 XXYYZZ13 8/25/2022 9/6/2022 AB10001 XXYYZZ14 8/...
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Anonymous ,

     

    You just need one measure for all of your requirements:

    _projectLeadTime = 
AVERAGEX(
    SUMMARIZE(
        yourTable,
        yourTable[Project No.],
        "minStart", MIN(yourTable[Start Date]),
        "maxEnd", MAX(yourTable[End Date])
    ),
    DATEDIFF([minStart], [maxEnd], DAY)
)

     

    Here's the output when applied against different levels of dimensions:

     

    Pete

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Project No.

Task Lead Time

Project Lead Time

AB10001

12

14

AB10001

12

14

AB10001

11

14

AB10001

11

14

AB10001

12

14

AB10002

43

74

AB10002

43

74

AB10002

41

74

AB10002

11

74

AB10002

12

74

AB10003

43

111

AB10003

43

111

AB10003

10

111

AB10003

72

111

AB10003

78

111

NikhilChenna's avatar
NikhilChenna
Skilled Sharer
3 years ago

Hi Anonymous ,

 

I think you can simply do a summarize table of the above table i tried it my self with the below code, you have to replace the table name , 

 

For creating a table, you have to go to the modelling tab and click on the new table as shown below,

 

 

Summarized_table5 =
SUMMARIZE(
'Table (5)',
'Table (5)'[Project No.],
"Max Start date",MAX('Table (5)'[Start Date]),
"Max End date",MAX('Table (5)'[End Date] ))
 
After this you can simply add a calculated column , Lead time - 
Lead time = Summarized_table5[Max End date]-Summarized_table5[Max Start date]
 
You' ll be able to  see individual how much time did they took project wise and if you total all three lead time values you'll get the combined number for it. 
 

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

  • NikhilChenna's avatar
    NikhilChenna
    Skilled Sharer
    3 years ago

    Hi Anonymous , If this solves your issue.

     

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!