Insert an index for a time range

[abartozzi]

Hi Team, 

have this question for you guys regarding a sort of index column. I have this (simplified) dataset for you, where the date range is expanded into different rows in order to reflect the daily duration of each product. 

Product name	Duration (days)	Duration (night)	Service Start (date)	Operation day (date)	End day (date)
X1	3	2	10/01/2021	10/01/2021	12/01/2021
X1	3	2	10/01/2021	11/01/2021	12/01/2021
X1	3	2	10/01/2021	12/01/2021	12/01/2021
X1	3	2	20/01/2021	20/01/2021	22/01/2021
X1	3	2	20/01/2021	21/01/2021	22/01/2021
X1	3	2	20/01/2021	22/01/2021	22/01/2021

what do I need to add to this is a sort of index column that counts the days as follows in this example:

Product name	Duration (days)	Duration (night)	Service Start (date)	Operation day (date)	End day (date)	Day no
X1	3	2	10/01/2021	10/01/2021	12/01/2021	1
X1	3	2	10/01/2021	11/01/2021	12/01/2021	2
X1	3	2	10/01/2021	12/01/2021	12/01/2021	3
X1	3	2	20/01/2021	20/01/2021	22/01/2021	1
X1	3	2	20/01/2021	21/01/2021	22/01/2021	2
X1	3	2	20/01/2021	22/01/2021	22/01/2021	3

what do I get is this

Product name	Duration (days)	Duration (night)	Service Start (date)	Operation day (date)	End day (date)	Day no
X1	3	2	10/01/2021	10/01/2021	12/01/2021	1
X1	3	2	10/01/2021	11/01/2021	12/01/2021	2
X1	3	2	10/01/2021	12/01/2021	12/01/2021	3
X1	3	2	20/01/2021	20/01/2021	22/01/2021	4
X1	3	2	20/01/2021	21/01/2021	22/01/2021	5
X1	3	2	20/01/2021	22/01/2021	22/01/2021	6

How do i get to the first chart result?

[ryan_mayu]

@abartozzi 

 you can try to create a column

Column = RANKX(FILTER('Table','Table'[Product name]=EARLIER('Table'[Product name])&&'Table'[Service Start (date)]=EARLIER('Table'[Service Start (date)])),'Table'[Operation day (date)],,ASC)

[abartozzi · Accepted solution]

Hi @PhilipTreacy  and @ryan_mayu 

As i needed to do that at query level (the next step was to merge a query with that column) i created a date difference between Operation day and service start +1 as Custom column in order to achieve "day no" column. 

The added column with RANKX was another idea I used as at the very beginning!

thank you for your hints

[PhilipTreacy]

Hi @abartozzi 

Which column is being indexed?  Service Start or End Day?

regards

Phil