Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Trying to show the value (bodyweight) for the most recent date for each individual, and if they don't have a weight for the most recent date in the data it will still pull from the last time they weighed in. Recently created a measure that worked but wouldn't show the ones that didn't have a weight for the most recent date, & hitting a roadblock with my current measure.
You need to compute the "most recent date" for each individual separately (for example via SUMMARIZE).
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Hi @lbendlin
I am interested in this solution too , so i prepared a sample table:
MemberDateValue
a | 01/02/2023 | 996 |
b | 02/02/2023 | 690 |
c | 03/02/2023 | 243 |
d | 04/02/2023 | 127 |
e | 05/02/2023 | 395 |
f | 06/02/2023 | 250 |
a | 07/02/2023 | 127 |
b | 08/02/2023 | 406 |
c | 09/02/2023 | 750 |
d | 10/02/2023 | 625 |
e | 11/02/2023 | 923 |
f | 12/02/2023 | |
a | 13/02/2023 | 222 |
b | 14/02/2023 | 622 |
c | 15/02/2023 | 201 |
d | 16/02/2023 | 151 |
e | 17/02/2023 | 828 |
f | 18/02/2023 | 751 |
a | 19/02/2023 | 567 |
b | 20/02/2023 | |
c | 21/02/2023 | 741 |
d | 22/02/2023 | 837 |
e | 23/02/2023 | 755 |
f | 24/02/2023 | |
a | 25/02/2023 | 748 |
b | 26/02/2023 | 894 |
c | 27/02/2023 | 112 |
d | 28/02/2023 | 53 |
e | 01/03/2023 | 202 |
f | 02/03/2023 | 975 |
a | 03/03/2023 | 651 |
b | 04/03/2023 | 112 |
c | 05/03/2023 | |
d | 06/03/2023 | 504 |
e | 07/03/2023 | 437 |
f | 08/03/2023 | 600 |
a | 09/03/2023 | 825 |
b | 10/03/2023 | 61 |
c | 11/03/2023 | 992 |
d | 12/03/2023 | |
e | 13/03/2023 | 922 |
f | 14/03/2023 | 393 |
a | 15/03/2023 | 784 |
b | 16/03/2023 | 500 |
c | 17/03/2023 | 173 |
d | 18/03/2023 | 716 |
e | 19/03/2023 | |
f | 20/03/2023 | 946 |
a | 21/03/2023 | 976 |
b | 22/03/2023 | 87 |
c | 23/03/2023 | 995 |
d | 24/03/2023 | 933 |
e | 25/03/2023 | 28 |
f | 26/03/2023 | 71 |
a | 27/03/2023 | 476 |
b | 28/03/2023 | 919 |
c | 29/03/2023 | |
d | 30/03/2023 | 919 |
e | 31/03/2023 | 534 |
f | 01/04/2023 | 432 |
a | 02/04/2023 | 323 |
b | 03/04/2023 | 498 |
c | 04/04/2023 | 758 |
d | 05/04/2023 | 523 |
e | 06/04/2023 | 818 |
f | 07/04/2023 | 611 |
a | 08/04/2023 | 537 |
b | 09/04/2023 | 56 |
c | 10/04/2023 | |
d | 11/04/2023 | 697 |
e | 12/04/2023 | 842 |
f | 13/04/2023 | 304 |
a | 14/04/2023 | 148 |
b | 15/04/2023 | 732 |
c | 16/04/2023 | |
d | 17/04/2023 | 91 |
e | 18/04/2023 | 95 |
f | 19/04/2023 | 314 |
Power Query version:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZVLbsJAEESvEnmNlOnPfPosKIsQ4P5HCOO0XDUxSyjjea9dbq7X7Xu7bEU+i35qUXt9iGjb1+W63WagFLQoe/AzA6NA3fbgPgOnQLTvwWMGlQKLugfPGTS+Vf07Y6fqb261Uw0KvDRQBQU9bzWppLCH1oNKFnO1g0rY/ONgksVb9WASX07Qg0nYW4uAib2lCpjYe+gA01j0BFTsXRsmpeVkMZmUrbuDSdl6GJ6e2nI0np762zlpXU4YIGLrEQ6m5WmLgomtq6FQLwnDYBWFUgqiVxTKKGg5v1uW1v6f/ZOltdXunpU9vq7FwdQp8JzfMyuLowuVPCgY2cxbVha/EPSJtSMwJ9ET6yMbi+sVYxKWtjC0iaX7cCCt2tgGwtrSsQ2Etbs0UMUJ9pl9Baw39Gmx7thQytajo06Ldq6bexYWgaFPytr01mlbHIC0PGtGYumQANNZehJZeXP9JDKWruZo0ytw1EzRJqXAcp3dsvr4RQw03CnodaDjlYKq9N41CoZgULP8jsZiUrP8uJXRIg8OsMdn9/08KGHtFthOwtrDqeOsbfmifmf5j0BoPwlrd6M93t4zsXTQEmdn+rcTVjZ5EX39Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Member = _t, Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Member", type text}, {"Date", type date}, {"Value", Int64.Type}},"en-GB"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Member"}, {{"Rows", each _, type table [Member=nullable text, Date=nullable text, Value=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Last Weight", each Table.Sort(Table.SelectRows([Rows], each ([Value] <> null)),{{"Date", Order.Descending}})[Value]{0},type number),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Member", "Last Weight"})
in
#"Removed Other Columns"
Measure:
This can be refactored in a couple of ways.
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |