cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgalla010 Frequent Visitor
Frequent Visitor

DATEDIFF with a Rankx column

Hi All! I'm not terribly experienced with nesting functions and I'm brain locked on how best to tackle this issue. I need to calculate the datediff for [Pay_Date] between [Rank_Order] in this data example. 

 

Here is the code I used to create the Rank_Order measure for reference.

 

Rank_Order =
VAR CurrentCustomer = Max(orders_grouped_mnth_dif[customer_id])
RETURN
RANKX(
FILTER(ALLSELECTED(orders_grouped_mnth_dif),orders_grouped_mnth_dif[customer_id] = CurrentCustomer),
CALCULATE(SUM(orders_grouped_mnth_dif[id])),
,ASC,Dense)

 

 

My plan is to use this to do further analysis on how quickly customers place the second and third order after placing their first. 

 

Thank you!

 

 

image.pngData Example

Ideally, the data output would be something like this:

 

Row 1 - Days_Diff would equal 24

Row 2 - Days_Diff would equal 111

Row 3 - Days_Diff would equal 0 (being Rank_Order 1)

5 REPLIES 5
VasTg Established Member
Established Member

Re: DATEDIFF with a Rankx column

@tgalla010 

 

I believe you are looking to create a column with number of days difference.

 

Try this.

 
Column =
VAR CURR_CUSTOMER = 'Table'[Customer ID]
VAR CURR_DATE = 'Table'[Pay_date]
VAR PREV_DATE = CALCULATE(MAX('Table'[First Order Date]),FILTER('Table','Table'[Rank]=1&&'Table'[Customer ID]=CURR_CUSTOMER))
RETURN DATEDIFF(PREV_DATE,CURR_DATE,DAY)
 
If it helps, mark it as a solution.
Kudos are good too.

 

amitchandak Super Contributor
Super Contributor

Re: DATEDIFF with a Rankx column

I think EARLIER should help you here

 

https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469

https://docs.microsoft.com/en-us/dax/earlier-function-dax

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

tgalla010 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with a Rankx column

Thank you VERY MUCH for taking the time to respond! Much appreciated! Using your approach, I implemented your code and PBI is has raised a circular reference error. Details below...might there be an easy fix? Thank you!

 

Number_of_days =
VAR CURR_CUSTOMER = 'orders_grouped_mnth_dif'[customer_id]
VAR CURR_DATE = 'orders_grouped_mnth_dif'[Pay_date]
VAR PREV_DATE = CALCULATE(MAX('orders_grouped_mnth_dif'[First_Order_Date]),FILTER('orders_grouped_mnth_dif','orders_grouped_mnth_dif'[Rank_Order]=1&&'orders_grouped_mnth_dif'[customer_id]=CURR_CUSTOMER))
RETURN DATEDIFF(PREV_DATE,CURR_DATE,DAY)
 
 
image.pngCircular Reference Error
VasTg Established Member
Established Member

Re: DATEDIFF with a Rankx column

@tgalla010 

 

Did you create the Rank column just to get the number of days differences or do you need that for some other calculations?

 

If the rank was created to get the no of days differences, I would delete it and use the below DAX to get the days differences.

 

Column =
VAR CURR_CUSTOMER = 'Table'[Customer ID]
VAR CURR_DATE = 'Table'[Pay_date]
VAR PREV_DATE1 = CALCULATE(MIN('Table'[Pay_date]),FILTER(ALL('Table'),'Table'[Customer ID]=CURR_CUSTOMER))
RETURN DATEDIFF(PREV_DATE1,CURR_DATE,DAY)
Highlighted
tgalla010 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with a Rankx column

Thank you @VasTg ! The formula you provided works however it calculates the difference from the latest date all the way back to the first. My goal is to calculate the difference between the sales. Thank you again for the very fast response! It's greatly appreciated!

 

image.pngExpected Result

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)