Forum Discussion
Conditional/Calculated Column for Sequencing
I have a dataset that contains annual upload information from users who use the same ID for every upload. I would like to create a conditional column (or maybe it is a calculated column?) that provides an order of reporting years for an upload, I believe this needs to happen during the data load because I need to use it for analysis and filtering. For example, looking at a datapoint in the second year of operation for all users.
I have
| ID | Reporting Period Start Date |
| AB1001 | 2018/01/01 |
| AB1001 | 2019/01/01 |
| AB1001 | 2020/01/01 |
| CD1002 | 2019/05/01 |
| CD1002 | 2020/05/01 |
And I would like to get
| ID | Reporting Period Start Date | Report Year |
| AB1001 | 2018/01/01 | 1 |
| AB1001 | 2019/01/01 | 2 |
| AB1001 | 2020/01/01 | 3 |
| CD1002 | 2019/05/01 | 1 |
| CD1002 | 2020/05/01 | 2 |
but then when AB1001 uploads their 2021/01/01 report, I would want the column to then add year 4 and when CD1002 uploads 2021, it would label that 3. And then so on and so on as new reports are continually added to the database. I am having issues finding a solution since the report start dates are not the same for everyone, so I can't label everything with a 2020 year 3 which seems to be most of what I am finding online.
Thank you in advance.
There are already some good answers to this out there. I'd recommend, for example, RADACAD
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
3 Replies
There are already some good answers to this out there. I'd recommend, for example, RADACAD
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query