topic Re: Create DAX index between 2 dates in a date table in DAX Commands and Tips

Create DAX index between 2 dates in a date table

eliottbbedard — Tue, 08 Aug 2023 15:16:28 GMT

Hi everyone,

I need some help with the creation of an index between 2 dates. I've created an automatic date table using the Calendarauto() function and I'd like to create an index in a calculated column between 2 dates (starting at 0).

I'm using the RANKX function as below to create an index starting on June 6, 2019 and ending on August 29, 2019 but the index is starting at 157 instead of 0.

Index 1 = VAR StartDate = DATE(2019,06,06) VAR EndDate = DATE(2019,08,29) Return IF( 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate, RANKX('Date','Date'[Date],,ASC))

Does anyone have any idea how I can achieve this?

Thank you for your help!

Re: Create DAX index between 2 dates in a date table

Greg_Deckler — Tue, 08 Aug 2023 15:19:45 GMT

eliottbbedard Try this:

Index 1 = VAR StartDate = DATE(2019,06,06) VAR EndDate = DATE(2019,08,29) Return IF( 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate, ( [Date] - StartDate ) * 1., BLANK() )

Re: Create DAX index between 2 dates in a date table

eliottbbedard — Tue, 08 Aug 2023 15:24:53 GMT

Greg_Deckler It works perfectly! Thank you very much for the quick response

Re: Create DAX index between 2 dates in a date table

tamerj1 — Tue, 08 Aug 2023 15:24:55 GMT

Hi eliottbbedard

You may filter 'Date' inside the RANKX for the same dates.