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 ...
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())
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.
camargos88
Community Champion
Community ChampionHi 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())