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

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.

Anonymous's avatar
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's avatar
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-juanli-msft 

      Thank you so much!