Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Date diff and Avg

Dear Sir

 

i have one table as shown:

i want to calculate avearge of delay in days

here some condition:,

final date=    if(actual date=blank,plan date,actual date)

delay= final date - forecast date

calculate Average of delay where delay>0 ??

 

How can i get this by measures ??

 

Need Help

3 Replies

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    4 years ago

    Hi Anonymous ,

    You can create a calculated column to calculate delay

     

    Delay =
    var finaldate=if(ISBLANK('Table'[Actual Date]),'Table'[Plan Date],'Table'[Actual Date])
    var delay=DATEDIFF('Table'[Forecast Date],finaldate,DAY)
    return delay
     
    And a measure like to calculate average delay:
    average delay = CALCULATE(AVERAGE('Table'[Delay]),FILTER('Table','Table'[Delay]>0))
     

    Please accept this as a solution if your question has been answered !!

    Appreciate a Kudos ğŸ˜€

    Connect with me on LinkedIn: https://www.linkedin.com/in/jaideepnema/

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      any way to do without creating calculated column ??

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Anonymous,

        You can add a variable with summarize function to summary table records and add the custom field with calculated expression, then you can use the iterator function to apply the second aggregations.

        Measure Totals, The Final Word 

        Regards,

        Xiaoxin Sheng