HELPPP ! Calculate Nb of days between 2 dates + calculate an average time with condition >7days

[Anonymous]

Dear all,

I need to calculate two things the first one is to calculate the number of days between two dates and the second one is to calculate an average time (days) between those 2 dates > 7days. 

Here are my two columns : Visit Date & Quotation Date. 

I tried to use the "DATEDIFF" function, but it does not allow me to select columns.

Could you please help me to calculate those two things ? 

Thank you !!!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

You can also use calculated columns to filter datediff

You said "I am not allowed to select columns", does it appear using measure? If you use measure to select columns, you need an aggregator, such as MIN or MAX, so that you can select columns

According to your description, I create this data:

Here are the steps you can follow：

1. Create calculated colum.

datediff = DATEDIFF('Table'[date1],'Table'[date2],DAY)

>7_column =
CALCULATE(AVERAGE('Table'[datediff]),FILTER('Table','Table'[datediff]>7)
)

2. Create measure.

>7_measure =
CALCULATE(AVERAGE('Table'[datediff]),FILTER(ALL('Table'),'Table'[datediff]>7))

3. Result

Filtering of Calculated column:

Filter of measure:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GregB49]

Hi @Anonymous,

The DATEDIFF function should work for this. Could you verify if your two columns are in a date type ? 
Little reminder of DATEDIFF : 
DATEDIFF( Start Date, End Date, Interval) 
In interval you can choose day, hour, minutes, seconds etc... So I guess in your case, it should by "Day" 

I'm not sure to understand the average part, could you be more precise ? 

Thanks, 

[Anonymous]

Hi Greg,

Thank you for your reply.

From the result of the formula you just mentioned, I need to know what is the average time of all the contracts which have a delay higher than 7 days.

For example :

XYZ600 Contract takes 36 days to go from "Visit Date" to "Quotation Date". This means that it is longer than 7 days

I need a formula to calculate the average time (ratio) of all contracts that have a delay of 7 days.

Thank you.

[GregB49]

@Anonymous 
Ok so the idea would be to create a new calculated column, 

Duration = DATEDIFF(Data[Visit Date],Data[Quotation Date],DAY)

Then a new measure : 

Ratio = CALCULATE( 
    Average(Data[Duration]),
    Data[Duration]>7)

It should do the trick. 
I made a simulation, please find the pbix attached.

https://we.tl/t-Oxl6a2hfwR 

[Anonymous]

Thank you so much Greg for your help ! 

As I said in my first message, I do not understand why the formula DATEDIFF can be filtered only with measure. 

I double checked the two columns are in a date type. 

[selimovd]

Hey @Anonymous ,

for me it sounds like you want to add it as a calculated column.

Try the following as a calculated column:

TimeDiff = DATEDIFF(myTable[Visit Date], myTable[Quotation Date], DAY)

Based on this column you can calculate a measure with the average time.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi