Forum Discussion
How to create an index
Hello, I would like to know how to create an index such index1 (see the example below) and that for each project in DAX (calculated column)
| Project | Fiscal Period | TargetDate | index1 |
| A | 202001 | 202008 | |
| A | 202002 | 202008 | |
| A | 202003 | 202008 | |
| A | 202004 | 202008 | |
| A | 202005 | 202008 | |
| A | 202006 | 202008 | |
| A | 202007 | 202008 | |
| A | 202008 | 202008 | 1 |
| A | 202009 | 202008 | 2 |
| A | 202010 | 202008 | 3 |
| A | 202011 | 202008 | 4 |
| A | 202012 | 202008 | 5 |
| A | 202101 | 202008 | 6 |
| A | 202102 | 202008 | 7 |
| A | 202103 | 202008 | 8 |
| A | 202104 | 202008 | 9 |
| B | 202001 | 202007 | |
| B | 202002 | 202007 | |
| B | 202003 | 202007 | |
| B | 202004 | 202007 | |
| B | 202005 | 202007 | |
| B | 202006 | 202007 | |
| B | 202007 | 202007 | 1 |
| B | 202008 | 202007 | 2 |
| B | 202009 | 202007 | 3 |
| B | 202010 | 202007 | 4 |
| B | 202011 | 202007 | 5 |
| B | 202012 | 202007 | 6 |
Hi Anonymous ,
Try this code for a calculated column:
Index = IF('Table'[Fiscal Period] >= 'Table'[TargetDate], RANKX(FILTER('Table', 'Table'[Project] = EARLIER('Table'[Project]) && 'Table'[Fiscal Period] >= 'Table'[TargetDate]), 'Table'[Fiscal Period],,ASC), BLANK())
4 Replies
- parry2k
Super User
Anonymous why there are empty values in index? What is the logic? You can use Rank to get index.
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Good Question.
If you look at project A, fiscal period start from 202001 and end in 202104. As you can see, I want the index when the fiscal period is equal or greater to the target date. So in project A, the target date is 202008, so we want an index starting at 1 for the fiscal period 202008 and the index continue to increase of one until 202104. we need to do the same for each project.
camargos88Community Champion
Hi Anonymous ,
Try this code for a calculated column:
Index = IF('Table'[Fiscal Period] >= 'Table'[TargetDate], RANKX(FILTER('Table', 'Table'[Project] = EARLIER('Table'[Project]) && 'Table'[Fiscal Period] >= 'Table'[TargetDate]), 'Table'[Fiscal Period],,ASC), BLANK())
