Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Index based on two date columns

Hello,   I have looked at other posts regarding index columns based on two columns, but i haven't been able to find a solution to my problem.    I have a DAX table with two date colomns (and some...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    This is done more easily in the query editor, but since you stated you need a DAX column, this one seems to work.  You'll need to replace Index with your actual table name.

     

     

     

    NewIndex =
    VAR vThisValue =
        INT ( CONVERT ( Index[Purchase date], DATETIME ) )
            + INT ( CONVERT ( Index[sales date], DATETIME ) ) / 100000
    VAR vAdd =
        ADDCOLUMNS (
            Index,
            "cValue",
                INT ( CONVERT ( Index[Purchase date], DATETIME ) )
                    + INT ( CONVERT ( Index[sales date], DATETIME ) ) / 100000
        )
    RETURN
        RANKX ( vAdd, [cValue], vThisValue, ASC )

     

    Note that I needed to use several Replace Values steps in the query editor to change month names to my locale (e.g., oktober, marts, februrary).

     

    Pat

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Assume they are date type column. A new column

 

countx(filter(Table, [Purchase date] <= earlier([Purchase date]) && [sales date] <= earlier([sales date]) ),[sales date])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Yes they are date type columns.

     

    I tried your DAX formula, but i get duplicate values. 

     

    My result looks like this 

    Purchase date         Sales date           index          
    juni 2020juni 20201
    juni 2020juli 20202
    juli 2020juni 20202
    juni 2020august 20203
    august 2020juni 20203
    juni 2020september 20204
    januar 2021juni 20204
    juli 2020juli 20204
    juni 2020oktober 20205
    marts 2021juni 20205
    august 2020juli 20206
    juni 2020november 20206
    april 2021juni 20206
    juli 2020august 20206
    juni 2020december 20207
    juli 2021juni 20207
    september 2020juli 20207
    juni 2020januar 20218
    juli 2020september 20208
    juni 2020februar 20219
    august 2020august 20209
    januar 2021juli 20209
    juli 2020oktober 202010
    juni 2020marts 202110
    september 2020august 202011
    marts 2021juli 202011
    juni 2020april 202111
    oktober 2020august 202012
    juli 2020november 202012
    juni 2020maj 202112
    august 2020september 202012
    juni 2020juni 202113
    april 2021juli 202013
    november 2020august 202013
    juli 2020december 202014
    juni 2020juli 202114
    august 2020oktober 202015
    juni 2020august 202115
    juli 2021juli 202015
    september 2020september 202015
    januar 2021august 202016
    juni 2020september 202116
    juli 2020januar 202116