Date diff and Avg

[Anonymous]

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 ??

 

[jaideepnema]

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]

any way to do without creating calculated column ??

[v-shex-msft]

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