Rank by time range and product

[Muertepelá]

Dear all, 

I have a simple table  with date colum and product, the products are manufactured by batches so I have several entries for product for diferents times range.  I would like to know how many times a product is manufactured  in a period of time, I put a example:

Product	Date	Rank expected
A	16/01/2020 22:56	1
A	17/01/2020 22:41	1
A	18/01/2020 6:36	1
C	18/01/2020 12:25	1
C	18/01/2020 20:20	1
C	19/01/2020 20:05	1
C	20/01/2020 3:59	1
C	20/01/2020 11:54	1
D	20/01/2020 12:57	1
D	23/01/2020 12:09	1
D	23/01/2020 20:03	1
D	24/01/2020 3:58	1
D	24/01/2020 11:52	1
D	24/01/2020 19:47	1
D	25/01/2020 3:41	1
A	25/01/2020 5:34	2
A	25/01/2020 13:29	2
A	25/01/2020 21:24	2
A	26/01/2020 5:18	2
B	26/01/2020 10:22	1
B	26/01/2020 18:17	1
B	27/01/2020 2:12	1
A	27/01/2020 3:55	3
D	31/01/2020 20:18	2
D	01/02/2020 4:13	2
C	01/02/2020 6:04	3
C	01/02/2020 13:59	3
C	01/02/2020 21:54	3
C	02/02/2020 5:49	3
C	02/02/2020 13:44	3
C	02/02/2020 21:38	3
C	03/02/2020 5:33	3
A	03/02/2020 12:55	2
A	03/02/2020 20:51	2
A	04/02/2020 4:46	2
A	04/02/2020 12:41	2
A	04/02/2020 20:37	2
A	05/02/2020 4:31	2

Any idea or suggestion it will be apreciated, 

Thanks in advanced

[amitchandak]

@Muertepelá ,Is it based on product and month ?? Can you explain the logic

In between

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[Muertepelá]

HI

Sorry there was a mistake on the previus table, apologies¡¡¡¡ Here the right one¡¡¡

Product	Date	Rank expected
A	16/01/2020 22:56	1
A	17/01/2020 22:41	1
A	18/01/2020 6:36	1
C	18/01/2020 12:25	1
C	18/01/2020 20:20	1
C	19/01/2020 20:05	1
C	20/01/2020 3:59	1
C	20/01/2020 11:54	1
D	20/01/2020 12:57	1
D	23/01/2020 12:09	1
D	23/01/2020 20:03	1
D	24/01/2020 3:58	1
D	24/01/2020 11:52	1
D	24/01/2020 19:47	1
D	25/01/2020 3:41	1
A	25/01/2020 5:34	2
A	25/01/2020 13:29	2
A	25/01/2020 21:24	2
A	26/01/2020 5:18	2
B	26/01/2020 10:22	1
B	26/01/2020 18:17	1
B	27/01/2020 2:12	1
A	27/01/2020 3:55	3
D	31/01/2020 20:18	2
D	01/02/2020 4:13	2
C	01/02/2020 6:04	2
C	01/02/2020 13:59	2
C	01/02/2020 21:54	2
C	02/02/2020 5:49	2
C	02/02/2020 13:44	2
C	02/02/2020 21:38	2
C	03/02/2020 5:33	2
A	03/02/2020 12:55	4
A	03/02/2020 20:51	4
A	04/02/2020 4:46	4
A	04/02/2020 12:41	4
A	04/02/2020 20:37	4
A	05/02/2020 4:31	4

I have a data set of thounsands,  The table is just for near a month, and in that month product A was manufactured 4 times, producto B was manufactured only once, product C was manufactured 2 times and product D was 2.  I would like to know how many times each product has been manufactured over time. 

Thanks in advance

[amitchandak]

Why for product A  16, 17,18 is 1 are we checking the sequence, when it becomes 2 when it becomes 3

[Muertepelá]

Hi

Rank column is that I would like to obtain. For first dates and product A, is 1, for next dates of product A, becomes in 2 , for next dates of product A becomes in  3 and so for each product. I have entries for each product when product changes and  undeterminate time.

Thanks

[ImkeF · Accepted solution]

Hi @Muertepelá  

that's a job for GroupKind.Local:

Please paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdNLrsMwCAXQrVQZVypcwI6Zvc8uqu5/G3XaKuCKaY64BEju9+1nu27cbsQ3EOgCuLXtcf1Az6AcsJ9AzeVd8bcCw2EVgBwUMDJQVICih7iNCpjd9AX/XzDn6AGSgUYFR3MJ0KX5XsHRHCUM19TcclRaYgZz0QpYHKMCsCNVtBzF79f9XYHn2lHC7twD4uYEZ0SPvqzEzgGF8xI5djWfEj4V6iznBTM0J62Al5snwHnzFyCizHVUMKO0rJhRsgdIjhI5J09wfFdWwZzc4rSkeXJtFfDyRyWYUdIDLEfJrHg8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Product"}, {{"Partition", each _, type table [Product=text, Date=text]}}, GroupKind.Local),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Product"}, {{"Partition", each Table.AddIndexColumn(_, "Rank", 1,1)}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows1", "Partition", {"Partition", "Rank"}, {"Partition.1", "Rank"}),
    #"Expanded Partition.1" = Table.ExpandTableColumn(#"Expanded Partition", "Partition.1", {"Date", "Index"}, {"Date", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition.1",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

Used techniques:

in step "Grouped Rows": https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/ 

in step "Grouped Rows1": https://www.youtube.com/watch?v=-3KFZaYImEY