cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Day difference bettween dates (and matching ids)

iddate visiteddays passednext visit
101/01/2018006/07/2018
203/02/2018005/08/2018
301/02/2018001/03/2018
301/03/20182805/02/2018
419/01/20180-
515/01/20180-
106/07/2018186-
305/02/20184-
205/08/2018183-

Hi, i want to do something like that table

1- it looks for the same id

2- dates passed : if it is the first visit (it goes a 0) if there is a visit before, it looks the previous visit and it calculates the date difference

3- it calculates the next visit by looking the id so i can make a visual with the amount that of days that is going to take

 

So how can i calculate the:

 

-days passed

-next visit

 

 

Thanks for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Day difference bettween dates (and matching ids)

Hi @Anonymous,

 

I made one sample for your reference. Please check the steps as below. 

 

1. Enter the sample data and sort the date visited and id columns in power query.

2. Insert an index column, Plese refer to the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY9LDsAgCETvwppEPtXSsxjvfw2ttga6acKGecMAtQIDghBbIh41GtrCmahAwwqyJUmkwTMqT496T8zRW4geXR4xP7WCDncQX0/QJNmT7AnHmxHYyss0rsC54POWLcKmv6x1", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"date visited" = _t, #"days passed" = _t, #"next visit" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"date visited", type date}, {"next visit", type date}, {"days passed", type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"id", Int64.Type}, {"date visited", type text}, {"days passed", Int64.Type}, {"next visit", type date}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"date visited", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"id", Order.Ascending}, {"date visited", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
    #"Added Index"

3. Create the measures as below.

dayps = 
VAR minind =
    CALCULATE ( MIN ( Table1[Index] ), ALL ( Table1 ), VALUES ( Table1[id] ) )
VAR mindate =
    CALCULATE (
        MIN ( 'Table1'[date visited] ),
        ALL ( Table1 ),
        VALUES ( Table1[id] )
    )
VAR maxid =
    MAX ( Table1[id] )
VAR maxindex =
    MAX ( Table1[Index] )
VAR nextid =
    CALCULATE (
        MAX ( Table1[id] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = maxindex - 1 )
    )
VAR maxdate =
    MAX ( 'Table1'[date visited] )
RETURN
    IF (
        minind = maxindex,
        0,
        IF ( nextid = maxid, DATEDIFF ( mindate, maxdate, DAY ) )
    )
next = 
VAR maxid =
    MAX ( Table1[id] )
VAR maxindex =
    MAX ( Table1[Index] )
VAR nextid =
    CALCULATE (
        MAX ( Table1[id] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = maxindex + 1 )
    )
VAR result =
    CALCULATE (
        MAX ( Table1[date visited] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = maxindex + 1 )
    )
RETURN
    IF (
        maxid = nextid
            && MAX ( Table1[date visited] ) <> result,
        result,
        BLANK ()
    )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
lnz
Frequent Visitor

Re: Day difference bettween dates (and matching ids)

May be this article would helpful for you
calculate avg time - between multiple rows

Community Support
Community Support

Re: Day difference bettween dates (and matching ids)

Hi @Anonymous,

 

I made one sample for your reference. Please check the steps as below. 

 

1. Enter the sample data and sort the date visited and id columns in power query.

2. Insert an index column, Plese refer to the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY9LDsAgCETvwppEPtXSsxjvfw2ttga6acKGecMAtQIDghBbIh41GtrCmahAwwqyJUmkwTMqT496T8zRW4geXR4xP7WCDncQX0/QJNmT7AnHmxHYyss0rsC54POWLcKmv6x1", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"date visited" = _t, #"days passed" = _t, #"next visit" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"date visited", type date}, {"next visit", type date}, {"days passed", type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"id", Int64.Type}, {"date visited", type text}, {"days passed", Int64.Type}, {"next visit", type date}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"date visited", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"id", Order.Ascending}, {"date visited", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
    #"Added Index"

3. Create the measures as below.

dayps = 
VAR minind =
    CALCULATE ( MIN ( Table1[Index] ), ALL ( Table1 ), VALUES ( Table1[id] ) )
VAR mindate =
    CALCULATE (
        MIN ( 'Table1'[date visited] ),
        ALL ( Table1 ),
        VALUES ( Table1[id] )
    )
VAR maxid =
    MAX ( Table1[id] )
VAR maxindex =
    MAX ( Table1[Index] )
VAR nextid =
    CALCULATE (
        MAX ( Table1[id] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = maxindex - 1 )
    )
VAR maxdate =
    MAX ( 'Table1'[date visited] )
RETURN
    IF (
        minind = maxindex,
        0,
        IF ( nextid = maxid, DATEDIFF ( mindate, maxdate, DAY ) )
    )
next = 
VAR maxid =
    MAX ( Table1[id] )
VAR maxindex =
    MAX ( Table1[Index] )
VAR nextid =
    CALCULATE (
        MAX ( Table1[id] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = maxindex + 1 )
    )
VAR result =
    CALCULATE (
        MAX ( Table1[date visited] ),
        FILTER ( ALL ( Table1 ), Table1[Index] = maxindex + 1 )
    )
RETURN
    IF (
        maxid = nextid
            && MAX ( Table1[date visited] ) <> result,
        result,
        BLANK ()
    )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Community Support
Community Support

Re: Day difference bettween dates (and matching ids)

Hi @Anonymous,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors