Ranking Data based on 2 Sort groups

[spudd]

Good Morning
Any ideas on how to do this in a measure.... Create a Ranking based upon a date from Earliest to Latest, where SALES DATE is Grouped and Machine is grouped.

My Data looks something like this, but continues for upto 42 days and involves some 30 other fields in the table but these are the fields I need. It is a direct query so cannot add to the table set in power BI
So Data looks like this

Salesdate	Machine Location	Time Start
24/02/2023	W7P1	23/02/2023 08:00
24/02/2023	W7P2	23/02/2023 06:00
24/02/2023	W7P1	23/02/2023 12:45
24/02/2023	W7P1	23/02/2023 19:00
24/02/2023	W7P1	23/02/2023 21:45
24/02/2023	W7P2	23/02/2023 10:45
24/02/2023	W7P1	23/02/2023 22:00
24/02/2023	W13P1	23/02/2023 09:52
24/02/2023	W13P1	23/02/2023 11:47
24/02/2023	W13P1	23/02/2023 12:56
24/02/2023	W13P2	23/02/2023 08:00
24/02/2023	W13P1	23/02/2023 14:57
24/02/2023	W13P2	23/02/2023 16:23
24/02/2023	W13P2	23/02/2023 08:00
24/02/2023	W13P1	23/02/2023 18:00
24/02/2023	W13P2	23/02/2023 19:32

I am looking to produce a result something like this with a Ranking... where salesates and machine are the same

Salesdate	Machine Location	Time Start	Ranking
24/02/2023	W13P1	23/02/2023 09:52	1
24/02/2023	W13P1	23/02/2023 11:47	2
24/02/2023	W13P1	23/02/2023 12:56	3
24/02/2023	W13P1	23/02/2023 14:57	4
24/02/2023	W13P1	23/02/2023 18:00	5
24/02/2023	W13P2	23/02/2023 08:00	1
24/02/2023	W13P2	23/02/2023 08:00	2
24/02/2023	W13P2	23/02/2023 16:23	3
24/02/2023	W13P2	23/02/2023 19:32	4
24/02/2023	W7P1	23/02/2023 08:00	1
24/02/2023	W7P1	23/02/2023 12:45	2
24/02/2023	W7P1	23/02/2023 19:00	3
24/02/2023	W7P1	23/02/2023 21:45	4
24/02/2023	W7P1	23/02/2023 22:00	5
24/02/2023	W7P2	23/02/2023 06:00	1
24/02/2023	W7P2	23/02/2023 10:45	2

 

[jdellasala]

Starting with your second table so I knew if I got it right!:

Book1
	A	B	C
1	Salesdate	Machine Location	Time Start
2	24/02/2023	W13P1	23/02/2023 09:52
3	24/02/2023	W13P1	23/02/2023 11:47
4	24/02/2023	W13P1	23/02/2023 12:56
5	24/02/2023	W13P1	23/02/2023 14:57
6	24/02/2023	W13P1	23/02/2023 18:00
7	24/02/2023	W13P2	23/02/2023 08:00
8	24/02/2023	W13P2	23/02/2023 08:00
9	24/02/2023	W13P2	23/02/2023 16:23
10	24/02/2023	W13P2	23/02/2023 19:32
11	24/02/2023	W7P1	23/02/2023 08:00
12	24/02/2023	W7P1	23/02/2023 12:45
13	24/02/2023	W7P1	23/02/2023 19:00
14	24/02/2023	W7P1	23/02/2023 21:45
15	24/02/2023	W7P1	23/02/2023 22:00
16	24/02/2023	W7P2	23/02/2023 06:00
17	24/02/2023	W7P2	23/02/2023 10:45
Sheet2

Brought it into Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ChangedTypeWithLocale = Table.TransformColumnTypes(Source, {{"Salesdate", type date}}, "en-GB"),
    ChangedType = Table.TransformColumnTypes(ChangedTypeWithLocale,{{"Machine Location", type text}}),
    ChangedTypeWithLocale1 = Table.TransformColumnTypes(ChangedType, {{"Time Start", type datetime}}, "en-GB"),
    GroupedRows = Table.Group(ChangedTypeWithLocale1, {"Salesdate", "Machine Location"}, {{"TS", each _, type table [Salesdate=nullable date, Machine Location=nullable text, Time Start=nullable datetime]}}),
    AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn( [TS], "Rank", 1 )),
    RemovedTS = Table.RemoveColumns(AddedCustom,{"TS"}),
    ExpandedCustom = Table.ExpandTableColumn(RemovedTS, "Custom", {"Time Start", "Rank"}, {"Time Start", "Rank"}),
    ChangedType1 = Table.TransformColumnTypes(ExpandedCustom,{{"Time Start", type datetime}, {"Rank", Int64.Type}})
in
    ChangedType1

Resulting in this table with a check against your values:

Book1
	E	F	G	H	I	J	K	L
1	Salesdate	Machine Location	Time Start	Rank		Match?		Source Ranking
2	02/24/2023	W13P1	02/23/2023 09:52	1		TRUE		1
3	02/24/2023	W13P1	02/23/2023 11:47	2		TRUE		2
4	02/24/2023	W13P1	02/23/2023 12:56	3		TRUE		3
5	02/24/2023	W13P1	02/23/2023 14:57	4		TRUE		4
6	02/24/2023	W13P1	02/23/2023 18:00	5		TRUE		5
7	02/24/2023	W13P2	02/23/2023 08:00	1		TRUE		1
8	02/24/2023	W13P2	02/23/2023 08:00	2		TRUE		2
9	02/24/2023	W13P2	02/23/2023 16:23	3		TRUE		3
10	02/24/2023	W13P2	02/23/2023 19:32	4		TRUE		4
11	02/24/2023	W7P1	02/23/2023 08:00	1		TRUE		1
12	02/24/2023	W7P1	02/23/2023 12:45	2		TRUE		2
13	02/24/2023	W7P1	02/23/2023 19:00	3		TRUE		3
14	02/24/2023	W7P1	02/23/2023 21:45	4		TRUE		4
15	02/24/2023	W7P1	02/23/2023 22:00	5		TRUE		5
16	02/24/2023	W7P2	02/23/2023 06:00	1		TRUE		1
17	02/24/2023	W7P2	02/23/2023 10:45	2		TRUE		2
Sheet2

Cell Formulas
Range	Formula
J2:J17	J2	=Rankings2[@Rank]=L2

I had to use Locale to get the proper data type in PQ, you should be able to remove those lines and use the existing Change Type steps to set the data types for the Salesdate and Time Start columns. The important steps are the grouping of the rows and adding an Index column to each group's table.
BTW, you should add what version of Excel you're using to your Profile, and use XL2BB when posting data. Makes life a lot easier for those that want to help.