Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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_IDDate
11-10-2018
11-11-2018
11-12-2018
11-1-2019
11-2-2019
21-2-2020
31-5-2020
31-6-2020
41-1-2018
41-2-2018
41-3-2018
41-4-2018
41-5-2018
41-6-2018
41-1-2019
41-2-2019
41-3-2019
41-4-2019
41-5-2019
41-6-2019
41-7-2019

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Sorry for replying late.

    Please create measures

    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 =
ADDCOLUMNS (
    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    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.

    Please explain the calculation rule.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-juanli-msft 

       

      Thanks for your reply. 

       

      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. 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    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

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        Sorry for replying late.

        Please create measures

        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 =
ADDCOLUMNS (
    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.