Forum Discussion

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

Need Help

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.

average date difference.pbix68 KB

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

average date difference2.pbix75 KB

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

Anonymous

Not applicable

6 years ago

Hi v-juanli-msft

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.

https://drive.google.com/file/d/1j0HxxkZddVkAIpJBjwuusiOVzVaeU7cF/view?usp=sharing

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.

average date difference.pbix68 KB

Anonymous
Not applicable
6 years ago
Hi v-juanli-msft ,

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] ) )

Anonymous

Not applicable

6 years ago

Hi v-juanli-msft ,

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?

v-juanli-msft

Community Support

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

average date difference2.pbix75 KB

Forum Discussion

Average date difference between two dates in same column

Related content

Average and Moving Average

Weekly Average

Accumulated average

moving average help

Timestamp difference average