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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    So as far as task is concern, the lead time is simple, start date - end date difference. But when it comes to project. I need the Difference between The date of latest end task & date of earliest started task. And finally after this, I need to view the total average of 3 Project,  Not as Line 1+2+3...+15 and divide by 15,