Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
id | date visited | days passed | next visit |
1 | 01/01/2018 | 0 | 06/07/2018 |
2 | 03/02/2018 | 0 | 05/08/2018 |
3 | 01/02/2018 | 0 | 01/03/2018 |
3 | 01/03/2018 | 28 | 05/02/2018 |
4 | 19/01/2018 | 0 | - |
5 | 15/01/2018 | 0 | - |
1 | 06/07/2018 | 186 | - |
3 | 05/02/2018 | 4 | - |
2 | 05/08/2018 | 183 | - |
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
Solved! Go to Solution.
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 () )
For more details, please check the pbix as attached.
Regards,
Frank
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 () )
For more details, please check the pbix as attached.
Regards,
Frank
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
May be this article would helpful for you
calculate avg time - between multiple rows
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |