Forum Discussion
Days difference between when the first items and second item was purchased
Hey PBI Experts - I need help calculating the day difference between when customers purchase the first and second items. e.g. - when a customer purchases a laptop for the first time and later comes ...
Hi,
This calculated column formula works
Column = if(ISBLANK(CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))),0,Data[sales_date]-CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date]))))Hope this helps.
Hi,
I am not sure how you arrived at those number - mine are different. I just copied the same calculated column formula which i shared with you ealrier and it worked fine. If you wish to include both days, just add 1
Column = if(ISBLANK(CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))),0,Data[sales_date]-CALCULATE(MAX(Data[sales_date]),FILTER(Data,Data[partner_key]=EARLIER(Data[partner_key])&&Data[sales_date]<EARLIER(Data[sales_date])))+1)Have you even tried my formula?
Ashish_Mathur Thanks for the Dax Expression. We had to create a column in the warehouse to bring in another updated_date, and i used the expression you provided in PBI and tweaked it to include the new column, and it works! Thanks 🙌
Ashish_Mathur - Here is the attached data and expected result. Thanks
| partner_key | partner_name | salesperson_key | sales_date | sales | currency_key | vendor_key | vendor_name | customer_key | product_key | quantity | expected_results_date_dif | Note: |
| 206448 | htl | 3002 | 2/25/2023 | 0.0651 | 356 | 4341 | cubic | 35898969 | 56503 | 3 | 0 | first purchase |
| 194440 | abc | 3059 | 2/19/2023 | 24.06 | 356 | 4341 | microsoft | 35223472 | 54510 | 3 | 0 | first purchase |
| 194440 | abc | 3032 | 3/15/2023 | 0 | 356 | 1236 | powerbi | 35572636 | 54510 | 25 | 28 | 28 days diffrence from first purchase |
| 189477 | bda | 3122 | 3/1/2023 | 88.22 | 356 | 4444 | jira | 35833258 | 54510 | 11 | 0 | first purchase |
| 189477 | bda | 3122 | 4/10/2023 | 48.12 | 356 | 6666 | spring | 35561694 | 54510 | 6 | 41 | 41 days diffrence from first purchase |
| 210782 | ccc | 3122 | 5/25/2023 | 208.25 | 356 | 5555 | aquafina | 35510920 | 57839 | 5 | 0 | first purchase |
| 210782 | ccc | 3482 | 6/20/2023 | 3.54 | 356 | 7777 | hawatour | 35214079 | 58484 | 1 | 26 | 26 days diffrence from first purchase |
You can try this measure for your desired output,
AverageVisits =
VAR CurrentDate =
FIRSTDATE ( 'Table'[SaleDate] )
VAR NextDate =
CALCULATE (
MIN ( 'Table'[SaleDate] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Id] ),
'Table'[SaleDate] > MIN ( 'Table'[SaleDate] )
)
)
VAR DateDiffference =
DATEDIFF ( CurrentDate, NextDate, DAY )
RETURN
IF(NextDate <> BLANK(), DateDiffference, 0)
This is the output I gotThis is the dataset that I've used.