Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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)
Proud to be a Super User!
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
Hi @abartozzi
Which column is being indexed? Service Start or End Day?
regards
Phil
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |