Forum Discussion
Days difference between when the first items and second item was purchased
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 - Sorry, and thank you for your time. The logic you gave was helpful but still did not return the desired output. Here is how i have come about the number: The numbers in the expected result column are days dif, the days dif between when a partner makes their first purchase from one vendor and the second purchase from another vendor. That's what I am trying to achieve. I am trying to see the day's diff between when a partner makes a first and second purchase from a different vendor. Thanks
Super UserPlease see my post dated June 29. The result there matches with your expected result.
Ashish_Mathur - Thanks! I have a feeling that the DAX you provided will work, its returns the exact day's diff in the dataset i provided, but it's returning something different in the real dataset I am working on. I need to do some updates on the report dates. I will let you know the outcome. Thanks again
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
You are welcome.