CODEWITHSUNDEEP
filterpowerbidaxpowerquerydatediff
WhoIsKi
WhoIsKi

Reputation: 117

calculate differences between 2 smallests date for each ID

I have some users with different game_id.

pic1

for each user, I want to find differences between the 2 smallest different dates and write the result in another column.

in this example, for user_id = 1, it should be 4 (difference between 13/2/2001 and 17/2/2001)

for user_id = 2, we don't have any result because we have just 1 date,

for user_id = 3, it should be 3

and for user_id = 4, it should be 3. (difference between 5/10/2003 and 8/10/2003)

How can I calculate it in Power BI?

Upvotes: 0

Views: 69

Answers (1)

horseyride
horseyride

Reputation: 21318

This does the trick with M code if you feel like using

It groups on user_id, gets the 2 lowest dates from the unique values, and calculates the difference. It then merges back on original data into a new column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"account_charging_dates", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"user_id"}, {{"Diff", each try Number.From( List.MinN(List.Distinct([account_charging_dates]),2){1} - List.MinN(List.Distinct([account_charging_dates]),2){0}) otherwise null}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"user_id"},#"Grouped Rows",{"user_id"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Diff"}, {"Diff"})
in #"Expanded Table1"

Upvotes: 1

Related Questions