Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Appreciate it if anyone could help me out with this (simple) task?
I am trying to replicate an exercise that I did on Excel, on Power BI.
The task can be broken down to two parts;
1) Last visit date-> Check if the current cell match with the data on column 'Name', if so, identify the penultimate date from column 'Order Date'
2) Days since last visit-> Calculate the difference between the current row's 'Order Date' and above penultimate 'Order Date'
I am sure this is a simple task but I could not get my DAX formulae to work properly.
My excel formulae for the two tasks are as follows;
1) Last visit date-> {=MAX(IF([Name]=[@Name],([Order Date]<[@[Order Date]])*[Order Date],""))}
2) Days since last visit-> {(=IF([@[Order Date]]-[@[Last visit date]]<1000,[@[Order Date]]-[@[Last visit date]],"")}
Appreciate it if anyone could help a DAX newbie. 🙂
Thanks,
Fonzy.
@Anonymous,
You may add calculated columns as follows.
Last visit date = MAXX ( FILTER ( Table1, Table1[Name] = EARLIER ( Table1[Name] ) && Table1[Order Date] < EARLIER ( Table1[Order Date] ) ), Table1[Order Date] )
Days since last visit = DATEDIFF ( Table1[Last visit date], Table1[Order Date], DAY )
Hi Sam,
Thank you for the response. I tried this, I am afraid, the formular is calculating wrong "Last visit dates". Any thoughts where the error could be coming from?
PS: Days since last visit calculation is also incorrect as it is connected to previous calculation.
Thanks,
John.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |