cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

How to change Date to Index

Basically, i am trying to 'Normalize' time. So the earliest available date per name starts at 1, and increases by 1.

 

Name    -     Date    -     (New Index Column) 

 

Joe              1/1/2017             1

Joe              1/2/2017             2

Joe              1/3/2017             3

Bob             1/1/2017             1

Bob             1/2/2017             2

 

..........

 

 

Basically, i am trying to plot Sales on the Y- axis, and "Working Days" on the X-axis.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: How to change Date to Index

Hi @aar0n,

 

Based on my test, you should be able to use the formula below to create calculate column in your table to add the index column in your scenario. Smiley Happy

Column =
VAR currentDate = Table1[Date]
RETURN
    CALCULATE (
        RANK.EQ ( currentDate, Table1[Date], ASC ),
        ALLEXCEPT ( Table1, Table1[Name] )
    )

c1.PNG

 

Regards

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: How to change Date to Index

Perhaps something like:

 

c_Index = DATEDIFF(MIN([Date]),[Date],DAY)+1

My have to reverse the first two parameters, I can never keep that straight...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: How to change Date to Index

Hi @aar0n,

 

Based on my test, you should be able to use the formula below to create calculate column in your table to add the index column in your scenario. Smiley Happy

Column =
VAR currentDate = Table1[Date]
RETURN
    CALCULATE (
        RANK.EQ ( currentDate, Table1[Date], ASC ),
        ALLEXCEPT ( Table1, Table1[Name] )
    )

c1.PNG

 

Regards

View solution in original post

Highlighted
Advocate II
Advocate II

Re: How to change Date to Index

Thank you so much!!! works perfectly!!

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors