Adding an index Column (or similar) for unique dates

[agd50]

I have not consecutive dates (fortnightly) and I want to add a column that will put a number next to each unique date. 

My aim is to be able to say - go back 26 fortnights (last year) and give me the value from that time and other time intelligence features. (There is not data inbetween the dates in the column).

[v-sathmakuri]

Hi @agd50 ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @agd50 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @agd50 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @danextian , @Khushidesai0109 , @prasannagoloju and @Ashish_Mathur  for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[danextian]

Hi @agd50 

In the query editor, sort your dates in ascending order. Wrap the formula created by the query editor in Table.Buffer - this is necessary so the sorted data is stored in-memory. Add index column which can start from either 0 or 1 from the Add column tab.

If your data has duplicate dates, you will need to remove them first before addin an index column or a date can have multiple index numbers instead of unique ones.

Another approach is to use DAX to rank the dates against each other.  This will assign a unique number to each date regardless of whether it repeats or not.

Date Rank = 
RANKX ( ALL ( WithDuplicates ), [Date],, ASC, DENSE )

Please see the attached sample pbix.

[Khushidesai0109]

Hiii @agd50 

• Load your data into Power Query Editor.

  • In Power BI, go to Transform Data to open Power Query Editor.

• Sort the Date Column (if needed).

  • Click on your Date Column → Go to Sort Ascending (to ensure correct order).

• Add an Index Column.

  • Go to Add Column → Click Index Column → Choose From 1 (or From 0 if preferred).

• Rename the Index Column to "Fortnight_Index" (or any name you prefer).

[prasannagoloju]

Hi @agd50 

use this calculated column to create Index column

FortnightIndex =
VAR UniqueDates = ADDCOLUMNS(
DISTINCT('Table'[Date]),
"Index", RANKX(ALL('Table'[Date]), 'Table'[Date],, ASC, DENSE)
)
RETURN
LOOKUPVALUE(
UNIQUE(UniqueDates[Index]),
UNIQUE(UniqueDates[Date]), 'Table'[Date]
)

and Measure for Fortnights

Value_26_Fortnights_Ago =
CALCULATE(
SUM('Table'[Value]),
FILTER('Table', 'Table'[FortnightIndex] = MAX('Table'[FortnightIndex]) - 26)
)

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.