Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I need help with creating a function.
This is my sample data:
session_start | session_user | Daily_sum |
9/20/2023 | Person 1 | 4 |
9/22/2023 | Person 1 | 5 |
9/25/2023 | Person 1 | 8 |
9/23/2023 | Person 2 | 3 |
9/25/2023 | Person 2 | 5 |
9/26/2023 | Person 2 | 4 |
9/27/2023 | Person 2 | 7 |
I need to find out the session_start date when the sum of each of the session_user goes below 10, calculated backwards from today.
In the sample data calculated from today (9/28/2023) backwards for Person 1 the date when the sum of Daily_sum values goes below 10 is 9/23/2023 (because 9/22/2023 adds 5) and Person 2's 9/27/2023 (because 9/26/2023 adds 4). I also need to be able to set how many days into the past the data is calculated.
I figured that I need to make the following two steps multiple times. First select the data that are in the past n days: (here 10 days) Table.SelectRows(Source, each Date.IsInPreviousNDays([session_start], 10)).
Then group those by "session_user":
Table.Group(Custom, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}).
Then I need to select the rows from the data that are in the past n - 1 days. Then group those by "session_user" and so on until today.
I was able to create a custom function that finds the value how many loops it takes for any of the values for session_users is below 10.
Here is the function:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}), CustomFunction = (n as number) as table => let FilteredRows = Table.SelectRows(#"Changed column type", each Date.IsInPreviousNDays([session_start], n)), GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}), Result = if List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(GroupedRows, "Final N Value", each n) else @CustomFunction(n-1) in Result, FinalResult = CustomFunction(10) in FinalResult
Here is the result at the moment:
The function has stopped because Person 1's sum is less than 10 but at the same time I need to find out when Person 2's sum goes below 10.
How can I achieve this?
Solved! Go to Solution.
Thank you for the reply!
I only want to take into account past days and not today so Date.IsInPreviousNDays should serve my purpose.
I've been able to refine my function as follows:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Promoted headers" =
Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}),
CustomFunction = (n as number, user as text, initialN as number) as table =>
let
FilteredRows = Table.SelectRows(#"Changed column type", each [session_user] = user and Date.IsInPreviousNDays([session_start], n)),
GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}),
Result = if Table.IsEmpty(GroupedRows) or List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(Table.AddColumn(GroupedRows, "Final N Value", each n), "First Try", each n = initialN) else @CustomFunction(n-1, user, initialN)
in
Result,
Users = List.Distinct(#"Changed column type"[session_user]),
DaysToFetch = 60,
FinalResult = Table.FromRecords(List.Combine(List.Transform(Users, each Table.ToRecords(CustomFunction(DaysToFetch, _, DaysToFetch))))),
Custom = Table.AddColumn(FinalResult, "Custom", each Date.AddDays( Date.From(DateTime.FixedLocalNow()),-[Final N Value]), type date),
#"Custom 1" =
Table.AddColumn(#"Custom", "not_valid_anymore", each Date.AddDays( Date.From(DateTime.FixedLocalNow()), if [First Try] = true then 0 else DaysToFetch - [Final N Value]), type date)
in
#"Custom 1"
This pretty much gets me what I need.
Danger Danger Danger.
Date.IsInPreviousNDays does not include Today !!!
Use a different function.
You also will want to group by person a lot earlier.
Thank you for the reply!
I only want to take into account past days and not today so Date.IsInPreviousNDays should serve my purpose.
I've been able to refine my function as follows:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tLs7Mz4svLkksKlHSgfNLi1OLgFyXxMycyvji0lylWJ1oJUt9IwMgMjIGygSkFhXn5ykYApkmMEkjLJKmMElTLJIWMEljNEkjINMYl04jZGPNsEjCHWSORdJcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Promoted headers" =
Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"session_start", type date}, {"session_user", type text}, {"Daily_sum", Int64.Type}}),
CustomFunction = (n as number, user as text, initialN as number) as table =>
let
FilteredRows = Table.SelectRows(#"Changed column type", each [session_user] = user and Date.IsInPreviousNDays([session_start], n)),
GroupedRows = Table.Group(FilteredRows, {"session_user"}, {{"Sum", each List.Sum([Daily_sum]), type nullable number}}),
Result = if Table.IsEmpty(GroupedRows) or List.Min(GroupedRows[Sum]) < 10 then Table.AddColumn(Table.AddColumn(GroupedRows, "Final N Value", each n), "First Try", each n = initialN) else @CustomFunction(n-1, user, initialN)
in
Result,
Users = List.Distinct(#"Changed column type"[session_user]),
DaysToFetch = 60,
FinalResult = Table.FromRecords(List.Combine(List.Transform(Users, each Table.ToRecords(CustomFunction(DaysToFetch, _, DaysToFetch))))),
Custom = Table.AddColumn(FinalResult, "Custom", each Date.AddDays( Date.From(DateTime.FixedLocalNow()),-[Final N Value]), type date),
#"Custom 1" =
Table.AddColumn(#"Custom", "not_valid_anymore", each Date.AddDays( Date.From(DateTime.FixedLocalNow()), if [First Try] = true then 0 else DaysToFetch - [Final N Value]), type date)
in
#"Custom 1"
This pretty much gets me what I need.
User | Count |
---|---|
9 | |
8 | |
6 | |
6 | |
6 |