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.
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.
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!
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)
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
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) |
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!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |