Calculate The Difference In Days Between Purchases Or Events In Power BI Using DAX

[@EnterpriseDNA]

***** Related Links ***** Last Purchase Date In Power BI: When Did Your Customers Make Their Last Purchase? blog.enterprisedna.co/when-did-your-customers-make-their-last-purchase-dax-techniques-in-power-bi/ How To Work With Multiple Dates In Power BI blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/ Calculate Rolling Totals Using DATESBETWEEN In Power BI blog.enterprisedna.co/calculate-rolling-totals-in-power-bi-using-dax/ ***** Related Course Modules ***** Advanced DAX Combinations portal.enterprisedna.co/p/advanced-dax-combinations Unique Analytical Scenarios portal.enterprisedna.co/p/unique-analytical-scenarios DAX Formula Deep Dives portal.enterprisedna.co/p/scenario-analysis-deep-dive ***** Related Support Forums ***** Calculating Difference Between Dates In Same Column With Multiple Events On Same Day forum.enterprisedna.co/t/calculating-difference-between-dates-in-same-column-with-multiple-events-on-same-day/2633 How Do You Calculate The Time Difference Between To Two Columns? forum.enterprisedna.co/t/how-do-you-calculate-the-time-difference-between-to-two-columns/1332 Frequency Of Product Purchased forum.enterprisedna.co/t/frequency-of-product-purchased/1135 For more difference in days queries to review see here….. forum.enterprisedna.co/search?q=difference%20in%20days

[@zaichushka]

Sam, this is very similar to what I need. I have to calculate the difference between restaurant reservations for the same customer. So I need to only count repeating customers. I have columns like PersonName, ReservationDate to use in a calculation. Data needs to be ordered by PersonName and then ReservationDate. Do you have any idea how to do that? Thanks!

[@anoosjun2634]

Hi Same where do you have the code for this section 4:57 - Working Out The Difference Between Purchases Thanks

[@zxccxz164]

this is awesome - do you have a sample PBI file???

[@rauljimenez5485]

I love the elegance of the concept to calculate the Prior Index Number!! This can be extrapolated to be used in so many other contexts. Thank you!!

[@ph3ll3r]

Very clever using indexing to link the customer purchases. Thanks for sharing.

[@UnknownShepard]

This is exactly what I needed. Thank you so much!

[@EnterpriseDNA]

Glad it was helpful! Please watch out for our 30 for 30 this month, that's 30 videos for 30 days! Don't forget to subscribe to our channel :)

[@juliotoro1482]

Thanks! Problem solved

[@EnterpriseDNA]

Hi Julio, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@JonathanCHANAL]

Thank you very much, very useful!

[@anoosjun2634]

Hi, Where to find the DAX function that are showed in the video.

[@thiagoambrosi]

Thanks Sam! How is it possible to calculate the total average of days between Purchases for a customer?

[@J_Tech_Wiz]

hi did you find a way to do this?

[@thiagoambrosi]

@@J_Tech_Wiz not yet ...

[@mshparber]

Thanks for sharing!

[@barathiventhanmurugan]

Hi team, I have already tried this one with the little different logic. I have paste the dax in the below : Only thing is I have used here Product Name instead of Location ID as my slicer. Days difference = VAR temp = TOPN ( 1, FILTER ( 'Personal Expense', 'Personal Expense'[Expense Product] = EARLIER ('Personal Expense'[Expense Product] ) && 'Personal Expense'[Full Date]< EARLIER ( 'Personal Expense'[Full Date] ) ), 'Personal Expense'[Full Date], DESC ) RETURN DATEDIFF ( MINX ( temp,'Personal Expense'[Full Date] ), 'Personal Expense'[Full Date], DAY )

[@VeganSmasher]

Mind = Blown

[@EnterpriseDNA]

Hi Tim, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@duong142]

Thanks a lot Sam. The solution is amazing. One question: I need to calculate the days between purchases for each customer so is there anyway to loop that one more condition in this formula ?

[@EnterpriseDNA]

Hi Duong, Sure you could be sure to check out the content below to help you. kzbin.info/www/bejne/h2nMo5xpfJaSrpY&lc=UgwViknf1kC_TpV00Qt4AaABAg kzbin.info/www/bejne/sHu2qYqFhLFsbqs Don’t forget to checkout the Enterprise DNA forum for any specific queries. forum.enterprisedna.co/ Regards, Enterprise DNA

[@anoosjun2634]

Hi, Where to find the DAX function that are showed in the video.

[@nataliaburello5679]

How can I do something similar to this but for each client? What I need is to count the days between purchase for each one so I can take the average

[@J_Tech_Wiz]

hi did you find a way to do this?

[@felipeandres8283]

Same, help please

[@J_Tech_Wiz]

Hi, i want to get the average of the number days for each customer, can you help?

[@wajdimatoussi]

Thanks a lot I have a question please and I hope you could help me I would like to create a calculated measure called TTD Term To date ..means the sum since the beginning of the semster ..in one year there are 2 semsters ( from jan to jun and from jul to dec ) Could you please help me ? I could not find a simple dax formula to perform that Of course it must be dynamic like the YTD depending on the period Thanks a lot

[@JonathanCHANAL]

Hello, how can I change this formula to get the same results but for each customer ID? Thank you very much ;)

[@EnterpriseDNA]

Hi Jonathan, If you build your model correctly you could quite easily place this measure against any number of attributes. The key to any advanced and powerful reporting in power bi is the data model. Be sure to check out the material below. kzbin.info/www/bejne/h5CogISDo6ambKs FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi Don’t forget to checkout the Enterprise DNA forum for any specific queries. forum.enterprisedna.co/ Regards, Enterprise DNA

[@JonathanCHANAL]

​@@EnterpriseDNA Thank you Sam, I'll check that later

[@kylebennett6]

I've watched the video twice now and I don't believe it's explained how the index column is actually calculated. Is it just an ascending column of integers, one per line? Is each integer exclusive to some other variable in the table? How should it be adjusted if there are repeating values in say, the order ID or product ID columns?

[@kylebennett6]

Ok, so I figured out that inserting an index column is basic functionality in Power Query. Got that far. However, my dataset is over 350k rows and this code is causing memory shortages and I have 32GB of RAM installed. Is there an alternative method to calculating this for large datasets?

[@EnterpriseDNA]

Hello Kyle, Thank you for posting your query onto our channel. The integers are assigned one transaction records per line and nothing else. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful. Also, make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/

[@zxccxz164]

Is there an updated version? This will not return and my FACT table only has 40k rows

[@zxccxz164]

Against 40k rows optimized model, it took 30 mins to return. it DOES return the correct answers. And once the page is loaded it does not take extra time to filter between customers. But it is not usable at this point when it takes 30 mins : ((

[@zxccxz164]

This will only work when you actually select a value from Data table (i am guessing that it why there is no File or demo of showing all orders then filtering down)

[@EnterpriseDNA]

Hello zxccxz164, Thank you for posting your query onto our channel and we really appreciate it. Well, these same measure can run over millions of lines of records and still will be able to evaluate the results so we're not sure here why it might not evaluate the results in your case. Also, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also, make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@rimthorn1958]

How can you get the grand total to represent the number of days in between first and last date?

[@rv7974]

You can use HASONEVALUE function. for example: If(HASONEVALUE('DayBetweenSteps'),'DayBetweenSteps',(DATEDIFF('FirsDate','LastDate'.day))). if I understood you correctly

[@rimthorn1958]

@@rv7974 On the Total row it shows -43792; This is not the total of DayBetweenSteps; How can you get the right figure?)

[@darrengodkin]

Another brilliant video, thanks for sharing. I have a similar tricky date challenge in Excel PP and BI data models. Got around it by shaping the data model beforehand with a vba data loop that runs all the way through 350k rows of data and asigns a index ID per block of Sales Date (over 2 dates) I'm pretty sure you would have a much easier method with variables and DAX, might be a great video off the back of it? Would you mind myself sending it over by g drive

[@J_Tech_Wiz]

@Guner Gardenhire following

[@zxccxz164]

@@rv7974 you have to use hasonevalue when your SSAS server is not on version that accepts SELECTEDVALUE