Select two latest values per attribute

[Pawel_C]

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?

[Vijay_A_Verma · Accepted solution]

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"

[Pawel_C]

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?

[Vijay_A_Verma]

I will need the sample data and output the way you gave in problem.

[Pawel_C]

I need a selection of rows like in the example below (no need to pivot, I can do that myself).

desired output

Here's my sample data

sample input

[Pawel_C]

Thank you @Vijay_A_Verma!
Wonderful solution!

[Pawel_C]

@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!

[Mahesh0016]

@Pawel_C  you want to below SS Result or...?

@Pawel_C  Thank You!!

[Mahesh0016]

@Pawel_C Please Can you Elaborate your EndOutPut? ThankYou!!