cancel
Showing results for
Did you mean:
Helper V

## Average date difference between two dates in same column

Hi everyone,

I want to calculate the average waiting time for our customers. The table shows a small sample set (see below).

We only have data once a month. To be precise as possible, I want to return the number of days for 0,5 month, because we don't know what the exact waiting time is, it could be 1 day or 31 days.

Notice that customer 4 has two periods of waiting time. How can we prevent that Power BI doesn't see this as one period, from 1-1-2018 to 1-7-2019, but sees it as two seperate periods?

The average waiting time is as follows:

Customer_ID       Waiting time in days

1                                 107,5

2                                 14,5

3                                 46

4                                 362,5

Average waiting time: 133

The order of date is European, so 1-10-2018 is October, 1st, 2018. For customer 1, the waiting period starts then. It ends at 1-2-2019, so 31 + 30 + 31 + 15,5 = 107,5 days.

 Customer_ID Date 1 1-10-2018 1 1-11-2018 1 1-12-2018 1 1-1-2019 1 1-2-2019 2 1-2-2020 3 1-5-2020 3 1-6-2020 4 1-1-2018 4 1-2-2018 4 1-3-2018 4 1-4-2018 4 1-5-2018 4 1-6-2018 4 1-1-2019 4 1-2-2019 4 1-3-2019 4 1-4-2019 4 1-5-2019 4 1-6-2019 4 1-7-2019

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support

## Re: Average date difference between two dates in same column

Hi @Johan94

``````lastmonth = CALCULATE(MAX('date'[year-month]),FILTER(ALLSELECTED('Table'),'Table'[CustomerID]=MAX('Table'[CustomerID])))

measure in days =
VAR days1 =
CALCULATE (
SUM ( 'date'[days_m] ),
VALUES ( 'date'[year-month] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[CustomerID]
= MAX ( 'Table'[CustomerID] )
)
)
RETURN
IF (
[lastmonth]
= MAX ( 'date'[year-month] )
&& [lastmonth]
<> BLANK (),
MAX ( 'date'[days_m] ) / 2,
days1
)

each days = SUMX(FILTER(ALLSELECTED('Table'),'Table'[CustomerID]=MAX('Table'[CustomerID])),[measure in days])

average dyas = SUMX(ALLSELECTED('Table'),[measure in days])/2``````

The date table used above

``````date =
CALENDARAUTO (),
"year", YEAR ( [Date] ),
"month", MONTH ( [Date] ),
"year-month", FORMAT (
[Date],
"yyyy-mm"
)
)

add a column in date table
days_m = CALCULATE(COUNT('date'[Date]),ALLEXCEPT('date','date'[year-month]))
``````

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

## Re: Average date difference between two dates in same column

Hi @Johan94

Is this ok?

``````lastmonth =
CALCULATE (
MAX ( 'date'[year-month] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[CustomerID]
= MAX ( 'Table'[CustomerID] )
&& 'Table'[country]
= MAX ( 'Table'[country] )
)
)

measure in days =
VAR days1 =
CALCULATE (
SUM ( 'date'[days_m] ),
VALUES ( 'date'[year-month] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[CustomerID]
= MAX ( 'Table'[CustomerID] )
&& 'Table'[country]
= MAX ( 'Table'[country] )
)
)
RETURN
IF (
[lastmonth]
= MAX ( 'date'[year-month] )
&& [lastmonth]
<> BLANK (),
MAX ( 'date'[days_m] ) / 2,
days1
)

each days =
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[CustomerID]
= MAX ( 'Table'[CustomerID] )
&& 'Table'[country]
= MAX ( 'Table'[country] )
),
[measure in days]
)

total eachdays =
IF (
ISINSCOPE ( 'Table'[CustomerID] ),
[each days],
AVERAGEX (
SUMMARIZE (
'Table',
'Table'[CustomerID],
"m", [each days]
),
[m]
)
)

``````

Best Regards

Maggie

11 REPLIES 11
Super User IV

## Re: Average date difference between two dates in same column

You will need some kind of group identifier column and then you could do something like MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Super User IV

## Re: Average date difference between two dates in same column

You might be able to use a variation of Cthulhu to get your group id column. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Community Support

## Re: Average date difference between two dates in same column

Hi @Johan94

For customer 2, he has only one date 2020/2/1,

for customer 3, he has a period from 2020/5/1-2020/6/1,

why the "waiting time in days" for them is 14.5, 46,

For customer 4, if we say he has a period from 2018/1/1-2019/7/1, then his "waiting time in days" should be 500+days.

Best Regards

Maggie

Helper V

## Re: Average date difference between two dates in same column

Customer 2 has only one date, because that customer wasn't anymore on the waiting list for 2020/3/1. But since we don't know when that customer wasn't anymore on the list on February, the number of waiting days is 14.5. This is our best approach/estimation we can make (29 days this year in Febrary, so 0.5 = 14.5 days).

So for customer 3 is it as follows: 31 days in May, 15 days (0.5*30) in June = 46.

Customer 4 appeared to be two times on the waiting list. 5.5 month in 2018 and 6.5 month in 2019. Between those two periods, the customer wasn't on the waiting list, hence those months should not be included. Therefore, the number of waiting days is 362.5.

I hope this helps.

Community Support

## Re: Average date difference between two dates in same column

Hi @Johan94

I get results as below:

Will work further to get the final result as you expected.

Before this, please check the results of your customer 1, if i undertsand your rule correctly, the result of your example is incorrect.

Please let me know if i undertsand correctly.

Best Regards

Maggie

Helper V

## Re: Average date difference between two dates in same column

Thanks for your help, I really appreciate it.

Unfortunately, that isn't the result I'm looking for. I created a sample set of the real data and with the outcomes the measure should give. I hope this helps.

Community Support

## Re: Average date difference between two dates in same column

Hi @Johan94

``````lastmonth = CALCULATE(MAX('date'[year-month]),FILTER(ALLSELECTED('Table'),'Table'[CustomerID]=MAX('Table'[CustomerID])))

measure in days =
VAR days1 =
CALCULATE (
SUM ( 'date'[days_m] ),
VALUES ( 'date'[year-month] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[CustomerID]
= MAX ( 'Table'[CustomerID] )
)
)
RETURN
IF (
[lastmonth]
= MAX ( 'date'[year-month] )
&& [lastmonth]
<> BLANK (),
MAX ( 'date'[days_m] ) / 2,
days1
)

each days = SUMX(FILTER(ALLSELECTED('Table'),'Table'[CustomerID]=MAX('Table'[CustomerID])),[measure in days])

average dyas = SUMX(ALLSELECTED('Table'),[measure in days])/2``````

The date table used above

``````date =
CALENDARAUTO (),
"year", YEAR ( [Date] ),
"month", MONTH ( [Date] ),
"year-month", FORMAT (
[Date],
"yyyy-mm"
)
)

add a column in date table
days_m = CALCULATE(COUNT('date'[Date]),ALLEXCEPT('date','date'[year-month]))
``````

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper V

## Re: Average date difference between two dates in same column

That's no problem! I am responding late myself.

I wanted to let you know that the measures worked. So, thank you very much for that. I did discover that the measure 'each days' didn't show the correct Total (it showed the number of the last row). This measures solves that:

each days_total =
IF(HASONEVALUE('Table'[CustomerID]);[each days];
AVERAGEX(
SUMMARIZE(
'Table';
[CustomerID];
"Measure";[each days]);
[Measure] ) )
Helper V

## Re: Average date difference between two dates in same column

I was wondering if it is possible to add another specification in the measure. For example, if you add a column to specifiy the country for each customer, let's say Spain and France (see image). I tried to change the first measure:

Spain measure in days = var days1 = CALCULATE(SUM('date'[days_m]);'Table'[Country] = "Spain";
VALUES('date'[year-month]);FILTER(ALLSELECTED('Table');'Table'[CustomerID]=MAX('Table'[CustomerID]))) return IF([lastmonth]=MAX('date'[year-month])&&[lastmonth]<>BLANK();MAX('date'[days_m])/2;days1)

And secondly, altering the next measure:
Spain each day = SUMX(FILTER(ALLSELECTED('Table');'Table'[CustomerID]=MAX('Table'[CustomerID]));[Spain measure in days])

But that makes no difference compared to the measure 'each days'... Is there a way to create this measure?

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors