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 other columns but they are not important in this matter)

One date column is the purchase date and the other column is the sales date. 

 

I would like an index column that looks something like this: 

Purchase date            sales date                      Index         
September 2020august 20201
September 2020september 20202
September 2020oktober 20203
September 2020november 2020 4
September 2020december 20205
September 2020january 20216
September 2020februrary 20217
September 2020marts 20218
September 2020april 20219
September 2020may 202110
September 2020june 202111
September 2020july 202112
September 2020august 202113
September 2020september 202114
October 2020oktober 202015
October 2020november 2020 16
October 2020december 202017
October 2020january 202118
October 2020februrary 202119

 

The logic is that the purchase month should be the primary index, and then the sales month. The index has to be continuos. My data is not sorted in this continuous fashion, which is the issue i am facing. And yes sometimes the sales date is earlier than the purchase date (don't ask why). 

I hope someone can help me solve this issue. The solution has to be a calculated DAX column. 

 

Thank you in advance. 

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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much, your solution worked!

  • 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