Forum Discussion
Select two latest values per attribute
Hi
I'm really rookie in terms of using DAX and PowerQuery, but need to solve a problem.
Having a table with data for each attribute in a daily manner:
(note! not all attributes are reported every day)
| DATE | Category | Value |
| 8/4/2023 | Beef | 123 |
| 8/3/2023 | Sheep | 456 |
| 8/3/2023 | Beef | 789 |
| 8/2/2023 | Sheep | 1234 |
| 8/1/2023 | Beef | 2345 |
| 8/1/2023 | Sheep | 4567 |
For each category I need to get to information on values from two latest available dates. (Format doesn't matter for now, I can pivot/unpivot the data to my needs).
| Date | Attribute | Value |
| latest available date | Beef | 123 |
| 2nd latest available date | Beef | 789 |
| latest available date | Sheep | 456 |
| 2nd latest available date | Sheep | 1234 |
Specific dates are "nice to have" but not necesary for this excersice.
I have tried to sort the data by date first and find dax formula to add the column with the number of occurence of each category, but ChatGPT wasn't very helpful with that and threw at me solutions which didn't work.
Do any of you have an idea to solve it?
Insert this step where #"Changed Type" should be replaced with your previous step
= Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"All", each Table.FromColumns(Table.ToColumns(Table.MaxN(_, "DATE", 2)) & {{"Latest available date", "2nd latest available date"}}, Table.ColumnNames(_)&{"Date Text"})}})[All])Complete code in action
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA30TcyMDJW0lFySk1NA1KGQE6sDkjGGCYTnJGaWgCkTUzN0KWgmswtLKEyRuiagOaZQOUM0XQBZUzRpZDsMleKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Category = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"Category", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"All", each Table.FromColumns(Table.ToColumns(Table.MaxN(_, "DATE", 2)) & {{"Latest available date", "2nd latest available date"}}, Table.ColumnNames(_)&{"Date Text"})}})[All]) in #"Grouped Rows"
8 Replies
Insert this step where #"Changed Type" should be replaced with your previous step
= Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"All", each Table.FromColumns(Table.ToColumns(Table.MaxN(_, "DATE", 2)) & {{"Latest available date", "2nd latest available date"}}, Table.ColumnNames(_)&{"Date Text"})}})[All])Complete code in action
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA30TcyMDJW0lFySk1NA1KGQE6sDkjGGCYTnJGaWgCkTUzN0KWgmswtLKEyRuiagOaZQOUM0XQBZUzRpZDsMleKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Category = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"Category", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"All", each Table.FromColumns(Table.ToColumns(Table.MaxN(_, "DATE", 2)) & {{"Latest available date", "2nd latest available date"}}, Table.ColumnNames(_)&{"Date Text"})}})[All]) in #"Grouped Rows"Thank you Vijay_A_Verma!
Wonderful solution!Hi Vijay,
Things got much more complicated for my selection.
On top of the 2 latest, I need to select "week ago" from the lastest found.
It's a problem I could solve IF this "week ago" day was always available. Sometimes it's not and I need to go 8 or 9 days back.
Do you think you can help me with that?I will need the sample data and output the way you gave in problem.
Pawel_C Please Can you Elaborate your EndOutPut? ThankYou!!
Mahesh0016 Thank you for reaching out.
Using my example above, I have 2 categories: "Beef" and "Sheep". Both reported irregulary in the format as in my input table.
I want to find for each of them, the latest value, and the one before it.
So e.g. "Sheep" wasn't reported on 8/4/2023, so it's latest report would be from 8/3/2023 with value 789. Sheep was also reported o on 8/2/2023 with value 1234, so I would like to get that as well.
Repeat the process for each category.
For clarity, let's assume that this is my final output table:Category latest date latest value 2nd latest date 2nd latest value Beef 8/4/2023 123 8/3/2023 789 Sheep 8/3/2023 789 8/2/2023 1234 Hope that helps!
