Find time between entries in same column

[Davidolis]

Hi,

I have a table which has 2 columns. One column (number) represents units with serial numbers that are unique. The other column (date - day) is which date the serial numbers are entered.

Each entry has one row. So a serial number will show up in multiple rows, depending on how often it is entered.

I want to find out how many days between each time a serial number is entered, in days. Then find the average for how often they are entered again. There is no limit for how many times a serial number can show up in the list.

I have tried grouping and other weird stuff, but my understanding of DAX is pretty weak. Could someone please help me out here?

[Nathaniel_C]

Hi @Davidolis , @amitchandak ,
This will get you part way.

Datediff =
VAR _curdate = 'serialN'[Column2]
VAR _prevDate =
    CALCULATE (
        MAX ( serialN[Column2] ),
        ALLEXCEPT ( serialN, serialN[Column1] ),
        ( serialN[Column2] ) < _curdate
    )
VAR _calc =
    CALCULATE (
        DATEDIFF ( _prevDate, _curdate, DAY ),
        ALLEXCEPT ( 'serialN', 'serialN'[Column1] )
    )
RETURN
    _calc

Get the current date, get the previous date, use DateDiff to calculate the time between.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

[Nathaniel_C]

Hi @Davidolis , @amitchandak ,
And the second column

Average time =
VAR _sum =
    CALCULATE ( SUM ( serialN[Datediff] ), ALLEXCEPT ( serialN, serialN[Column1] ) )
VAR _count =
    CALCULATE (
        COUNT ( serialN[Column1] ),
        ALLEXCEPT ( serialN, serialN[Column1] )
    ) - 1
RETURN
    DIVIDE ( _sum, _count )

Get the sum, get the count - 1, Divide for average.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[amitchandak]

Try EARLIER

https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469

https://docs.microsoft.com/en-us/dax/earlier-function-dax

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks

[Davidolis]

Hi, sorry if it wasn't clear i will make an example

123456789	1.1.2018
987654321	2.5.2019
124578945	5.5.2018
123456789	4.5.2019
554123479	6.6.2019
987654321	10.11.2019
123456789	9.11.2019

An entry can show up multiple times and I need to know how long on average between each entry and then average for all. Is this helpful? I haven't attempted the proposed solutions yet, just thought i would do my part first.

[Nathaniel_C · Accepted solution]

Hi @Davidolis , @amitchandak ,
The last column is the average overall.

AverageX = AVERAGEX(serialN,IF(serialN[Datediff]=BLANK() && serialN[Average time]>0,serialN[Average time]))

which will also work as a measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Davidolis]

@Nathaniel_C This is great, Thank you so much! 

I had some problems getting the last one to work, but it needed a ; instead of a, in the last parameter for the IF. 

Thank you so much for helping me out! The examples really let me walk through it and understand how to do it. Without that table i would not have been able to recreate it...😕 Thanks again Nathan!

[Nathaniel_C]

Hi @Davidolis ,
You are very welcome! Thank you for your kind words. DAX is pretty amazing stuff.
Nathaniel