Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
skanord
Advocate II
Advocate II

Index based on two date columns

‎09-20-2021 02:53 AM

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. 

Labels:
Message 1 of 7
553 Views
0
1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

‎09-20-2021 05:17 AM

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.

 

mahoneypat_0-1632140122017.png

 

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Message 6 of 7
436 Views
6 REPLIES 6
mahoneypat
Employee
Employee

‎09-20-2021 05:17 AM

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.

 

mahoneypat_0-1632140122017.png

 

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Message 6 of 7
437 Views
skanord
Advocate II
Advocate II
In response to mahoneypat

‎09-20-2021 06:58 AM

Thank you so much, your solution worked!

Message 7 of 7
413 Views
0
amitchandak
Super User
Super User

‎09-20-2021 03:04 AM

@skanord , Assume they are date type column. A new column

 

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



Message 2 of 7
534 Views
0
skanord
Advocate II
Advocate II
In response to amitchandak

‎09-20-2021 03:13 AM

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

 

Message 3 of 7
513 Views
0
amitchandak
Super User
Super User
In response to skanord

‎09-20-2021 03:19 AM

@skanord , I that case add an index column in power query

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi



Message 4 of 7
485 Views
0
skanord
Advocate II
Advocate II
In response to amitchandak

‎09-20-2021 03:39 AM

How can i do that when it is a dax calculated table?

Message 5 of 7
474 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All