DAX Fridays! #85: Difference between dates in the same column
ฝัง
- เผยแพร่เมื่อ 7 ก.ค. 2024
- Do you know how to calculate the difference between dates when both are in the same column?
One example of when this is needed is when you are working with subscription based services. If you want to calculate how many dates go between users changing subscriptions, this DAX measure will help you.
To do this we will use DATEDIFF, ALLEXCEPT and EARLIER.
and here is a link to Chris Webb trick :
blog.crossjoin.co.uk/2018/06/...
Enjoy.
Link to the file here: curbal.com/blog/glossary/date... (example 3)
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Looking for a download file? Go to our Download Center: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
Hi, a similar way to obtain this result will be:
DaysDiff =
VAR SubscriptionCreatedContext = Test[Subscription Created]
VAR MaxBefore_SubscriptionCreatedContext =
CALCULATE (
MAX ( Test[Subscription Created] );
FILTER (
Test;
Test[ID] = EARLIER ( Test[ID] )
&& Test[Subscription Created] < SubscriptionCreatedContext
)
)
RETURN
IF (
MaxBefore_SubscriptionCreatedContext = BLANK ();
DATEDIFF ( Test[Account Created]; SubscriptionCreatedContext; DAY );
DATEDIFF (
MaxBefore_SubscriptionCreatedContext;
SubscriptionCreatedContext;
DAY
)
)
Thanks for sharing!!! :)
/Ruth
Hi Victor!
Some questions:
1. As you are using variables, am I correct to assume that this expression works as a measure, without the aid of a calculated column?
2. In the first variable, shouldn't the column be inside an statistical function such as MAX?
I'm trying to apply this formula in one of my reports, hope it works!
Hi Fernando. Answer to Question 1. Is a calculated column, you can use variables in measures and calculated columns.
Answer to Question 2. In a calculated column you can refer the name of the column.
Let me know if need more help send me a email to vvelardeb@gmail.com
Victor, I just tried with a calculated column and it worked!
My report was a bit trickier because it envolves a "datediff" between DateTimes, not only dates.
For some reason that I still don't understand, syntax that Ruth shared with us causes some kind of overload, but this syntax worked nice and quickly :)
I can't thank enough to you!
I know this is late but what is "Test".
A well needed rescue 4 years later ... THANK YOU 😁
Hi Ruth, Just wanted to say hello and thank you for such a great example, I've been looking for a solution related to this "issue" and this worked perfectly. Thank you again for sharing your knowledge.👍🏼
You dont know how much i owe to you. You just have sooved my issue for which i was struggling for 3-4 days. Your channel is awesome
🥳🥳
Hello, you’re absolutely amazing with all the videos, you do it soo easily so even me a novice can understand it, Thank you so much
😊 Thanks!
/Ruth
and yet another lifesaver from Curbal :-) Thnx!
Ruth, muchísimas gracias. Como siempre es fantástico seguir aprendiendo de tu "DAX sabiduría". Me encantan tus florecillas !!!!!
😂😂 Muchas gracias Maria!!
Son bonitas verdad? Aquí en el solsticio de verano se ponen flores en el pelo 😊
/Ruth
Fabulosas!!! Aquí en Mallorca es tradición ir a la playa con lucecitas (noche de San Juan) para celebrar el solsticio (día más largo y la noche más corta)
En Asturias prendemos hogeras y la gente quema (o quemaba) los “malos rollos” , buenas tradiciones 😉!
/Ruth
Hi Ruth. Your descriptions and example related to DAX problem solving is really geat
Thanks!
Ruth, fantástico tu video. Entretenido y profundo respecto de DAX. Me he dejado como objetivo para hoy, lograr comprender cada detalle y ejercitar luego. Que tengas un excelente día.
Ha perfecto! Espero que lo haya explicado bien para que sea un viven ejercicio!
/Ruth
Super video - really clear explanations. I have a problem to solve which is almost exactly like your example and I will be able to use this. Thank you very much.
Glad to hear and good luck with your DAX problem :)
/Ruth
Exactly what I needed!! Thank you!
excellent example and great explanation skills! great video.
Thanks Jason! :)
/Ruth
the "EARLIER" function is not working for me, anyone know why?, also "if" is not returning any column aswell.
You Know You are AWESOME LOVE YOU... keep making videos
Very interesting example and extremely good explanation
Thanks!!! Appreciate it :)
/Ruth
Thank you!! Amazing explanation.
The first calculation did not work for me in a real dataset. I get out of memory error. Check it multiple times for accuracy and still not working.
Excelente video ruth
Thanks! And Happy midsommar!
/Ruth
amazing video!!! it helped me to create a date range column from a give date column. Thanks mam
Yey!! Congrats ;)🎉🎈
/Ruth
Thanks very much ..... it is really useful
Lovely!Solved my problem!Thank you!
Yey!!’ 🎉🎉🎉
Hi Ruth,
Is there a way to get only the workdays when we calculate the day difference column?
Thanks!!
Thank you for this!
You welcome!
/Ruth
Another video in was explained variables and complicated DAX measures in an easy way. The use of MAX and ALLEXCEPT to group columns was optimal. By the way, enjoy the summer because I'm in the same situation as Matt Allington: in winter.
I am heading there, so wait for me!
/Ruth
Hello Ruth, I am trying to accomplish this with a Direct Query Data source. The problem is many functions don't work (Like Calculate, Earlier) with Direct Query, do you have any suggestions or other alternatives for this?
Wonderfull and usefull, Thank you Ruth
You welcome ! And Happy Friday :)
/Ruth
Excelente Explicacion Ruth, como siempre, muchas gracias
En mi caso , ademas de necesitar el calculo entre fechas en una misma columna, pasa que la misma fecha puede referirse a dos status diferentes y debo solo seleccionar una de ellas. Tienes algun tutorial al respecto?
Hi Ruth, Thank you for such a great example, any way to exclude Weekends ?
You are AWESOME!!!
👏👏👏👏
Hello, Very helpful video.
Is there a way we can show the value as zero if the previous date is same as the date in the current step?
Currently, if the previous date is same, it is skipping the date and looking the the date before that.
But I want to show as zero if the dates are same
Amazing 🙇
Thank you Ruth, nice solution but difficult. To figure out by yourself. Would it be possible to make a more simple solution , for example to make a helper collum: with "new" and "existing" and another IF formula to refer to this?
Everything is allowed in DAX until you hit performance issues, so yes, If you can make it work, that is the way to do it :)
/Ruth
i have date format issue. can you telll me how to resolve it
Hi Ruth,
Very good video. I particularly like the fact that your videos a 'real and by that I mean, your videos include your thinking out loud and not just the edited outcome.
Do you have the sample file please?
Good work!
😂😂 Nice that you noticed that! Yes, i try to keep them as raw as possible, so you can see the “real experience”.
Thanks the feedback, glad you like the format!!
/Ruth
Oh, I will publish the link on Monday! Still on mini-vacation :)
/Ruth
I know this is very old but I really need your help. While this example is very instructive, for my case it is too complex. All I need to do is find the interval between two datetimes that are in the same column. I'm sure that somewhere in this example is the answer to my question but for some reason I can't find it. This issue has plagued me for months!
Thanks Curbal! You just helped me streamline workflow for a big project. Wish I could give details, but-ye know-laws :(
I know too well :(
Wish I could show more of my work too!
We couldn't found the File to download in additon Earlier Not Working
It worked correctly for me, this is what I was looking for, but how could I calculate the days that are working days, let's say, count the days worked from Monday to Friday and omit the weekends.
I was thinking of inner join but that didn't work out as there is only 1 column for "id". This solved my issue. Thanks a lot lovely lady :)
The pleasure is all mine ;)
How do u exclude weekends and public holidays.
Hey, I tried to reproduce this for a dataset of 62 records but my 8 GB Ram is not enough to run the calculated column with the earlier function. Even with 24 GB Ram I guess the problem will persist next month! Any Ideas to overpass the issue?
Details are needed to answer that question, can you post in the power bi community given sample data? Thanks
/Ruth
Hi Ruth, great video as always!
Maybe it would be better to create the calculated columns using Power Query instead of using DAX. For really huge tables, I think that, performance wise, power query would perform better because the calculation happens only once (when the data refreshes) - while on DAX it's calculating all the time the columns are instantiated.
Well, this is how I would approach this.... maybe it's a good idea for your next video (create the same calculated columns using Power Query/M language) :)
Thanks!
Thanks, that is a great suggestion :)
/Ruth
I would love to see this function in PQ too, may have a wee a go if i get a chance. Perhaps even a function which just pulls the previous in sequence date for a given parameter. Would help with a lot of the calculations i tend to do. for reference my current method of doing it running a pivot table organised by date and then numbering each row/event in its group and sequence by n+1
thanks
Thanks!!!
Just have to find a solution to very similar scenario.
Will go and give it a try.
Good luck! You will crack it :)
/Ruth
Unfortunately it didnt help me :-( even with "small" table of 50K rows it killed my computer.
For me the LOOKUPVALUE + index column work OK - e.g.:
PreviousStatusID =
IF (
LOOKUPVALUE ( 'Status'[ContID], 'Status'[Index], 'Status'[Index] - 1 )
= 'Status'[ContID],
LOOKUPVALUE ( 'Status'[StatusID], 'Status'[Index], 'Status'[Index] - 1 ),
BLANK ()
)
😂😂 There is the comments an alternative solution if you want to try that :)
/Ruth
I review all the comment but couldn't find :-(
Victor Velaverde wrote it like 3 comments up. I cant Link to it as I am in the youtube app.
/Ruth
Hi, thanks for this video 😊
I have one question, at the end of the video you created a measure for calculate "DATEDIFF" but how can I use the "earlier" function? I can't use it in a measure.
Thank youuu 😊
Earlier works only in calculated columns. The how to will depend on what you are trying to do and other factors. Post in the power bi community!
Hi Paola, can u fix it? im with the same problem
What if you are looking for time in minutes and not days. Thanks. This was a helpful video.
Thanks Ruth,
Great Video! Have you posted any videos that completed this Datediff calculation, but only using the one Subscription Column to get the datediff? So regarding the Account create Column. Thanks again Adam
No, I don't think so 🤔
Thats cool, I simply need the datediff summing up in last column only. Let me know if you think of anything. Top work!
@@CurbalEN managed to complete datediff from one column only and index column. :)
DateDiffFinal =
VAR StartDate =
CALCULATE (
LASTDATE ( 'SCF4 PRE-CON-MSC-TIME'[end date] ),
FILTER (
ALLEXCEPT ( 'SCF4 PRE-CON-MSC-TIME', 'SCF4 PRE-CON-MSC-TIME'[Project ref] ),
'SCF4 PRE-CON-MSC-TIME'[end date]
< EARLIER ( 'SCF4 PRE-CON-MSC-TIME'[end date] )
)
)
RETURN
DATEDIFF (
StartDate,
'SCF4 PRE-CON-MSC-TIME'[end date],
DAY
)
Fabulous and thanks for sharing:)
Hola Ruth, con que funciones puedo realizar una resta de fechas entre dos columnas, pero que me lo devuelva en una tabla, con los días transcurridos por mes.
Ejemplo Fecha Inicio= 1/1/2018, Fecha Fin= 31/3/2018
Resultado
MES | Dias
Enero | 31
Febrero | 28
Marzo | 31
Hola, lo haría en Power query si es posible.
Pon la pregunta en la communidad de power bi (puedes escribir en español) para recibir el código.
/Ruth
I LOVED !!! CONGTS ....
🥳🥳🥳
/Ruth
Hi ruth, great explanation. I tried it, but I have an error with earlier. It gives me an error. Put it in daxformatter, but it says its ok. Do you have file to share, so i can check it with mine. Thx. Also happy midsummer. Keep dancing around the maypole 😀😀
I forgot the link to the file? Ouch! I will update it as soon as possible.
Regarding EARLIER, make sure you use it in a calculated column, not in a measure :)
/Ruth
Ahaaaaaaa, i saw i think my mistake. In the explanation, you started with a measure. But after point 14:30 it has become a column calculation. , after you say enter its a column. 😂😂
Hello,
Thanks a lot but I'm not able to enter the last formula. I chose new measure but for the second variable it says (for the part
Earlier works row by row. When putting Earlier in a measure , it is unable to do that so it gives you an error. With calculated columns you can do toe by row calculations and that is why it works.
Hopefully this explains (in broad terms)
/Ruth
@@CurbalEN Thank's It is working now
Regards Olivier
Hi Ruth , grate video as always, thanks.
I have the next question.
It is possible to calculate the total of sales from last month on a certain days with dax?
Example:. Let's say that we are in the month of Jun and I want to calculate the total sales of the month of May from the day 20 to the day 30 .
It's that possible?
Thanks
Veo comentarios en español , No sabía que hablabas español.
Lo que quiero decir en este comentario , es que tengo una tablas de ventas y necesito sumar las ventas del mes pasado solo de días específicos , por ejemplo ,si estamos en junio quiero sumar cuanto vendi en el mes de mayo del día 20 al dia 30 .
Gracias
Hola Eduardo! Si, si soy española :)
Podrías poner la pregunta en la comunidad de Power Bi, pon ejemplo de tus datos también :)
/Ruth
I think it's this. Works for me. Interesting to see what would happen if the date is say March 30th and there is no equivalent day in Feb from which to take the number. I think it would use Feb 28th (or 29th in a leap year)
Sales Actual or Last Month =
//Get single value for Date when dates are on rows
VAR vDate = FIRSTNONBLANK('Date'[Date], TRUE())
//vPMSales = Previous month's sales on the same day number
VAR vPMSales = CALCULATE([Sales], DATEADD('Date'[Date], -1, MONTH))
RETURN
//If the date (on the row) is yesterday or before then use the measure [Sales]
//otherwise use vPMSales
IF(
vDate
Wonderful, thanks David!!
/Ruth
Yes for March 29th, 30th and 31st DATE('Date'[Date],-1,MONTH) does look back to 28th Feb
The Danish Midsummer is "Sankt Hans" and is always on June 23th. Thus, not flexible as the Swedish.
PS. 2 matches and 4 points to Denmark
Same for 🇪🇸!! Och glad midsommar!
/Ruth
excellent
Thanks!
/Ruth
HI. I am a huge fan of your channel & I appreciate all the creative content you provide.
I have a question; in a sales table, many steps of each order are indicated in a column. eg., order created, order paid, call center confirmed, stock confirmed, packing confirmed, invoiced, shipped, delivered. The date-time of each of these steps is indicated in another column.
I need to assess the duration between these steps to evaluate the performance of each team. except for delivery and payment. (because these are not related to any internal team)
I think I should create another table that only includes the steps that I need and then extract the Date-Time of each step in a separate column, then subtracts each consecutive step.
Or maybe use your method in this video, but extract the date-time of each step in a separate column, so I can name the duration. (I mean to distinguish them for which step is it)
I am getting errors in both methods, and I am trying to handle them. But, do you think this is an appropriate method or I am going the wrong way?
I appreciate it if you could guide me, thank you in advance.
The power bi community is the best place for this type support !
Cool !!
Thanks :)
/Ruth
Hola Ruth: ¡Fantástico tutorial!.
Muy útil para alguno de mis informes en Power BI. Muchas gracias por compartir tus conocimientos, que tanto nos ayudan.
Siguiendo la línea del tutorial, quería plantearte una duda que me ha surgido en el momento de implantar tu explicación en uno de mis informes: ¿cómo se podrían omitir filas si las fechas se encuentran en un periodo de tiempo concreto?. En mi caso se trata de controlar los accesos de personal y a veces una misma persona se registra dos veces el acceso en cuestión de segundos, es decir, pasa varias veces la tarjeta acceder y se registran todos estos accesos cuando realmente solo es uno.
Aplicando la fórmula de tu tutorial he optimizado el registro de accesos, pero quisiera incorporar una condición para que no incluya los accesos que hace una persona, si se repiten en un intervalo de 30 segundos, y no sé como hacerlo.
No sé si puedes ayudarme.
Gracias anticipadas, de un vecino de Galicia.
Saludos.
Hola vecino!
Ahh, podrías poner la pregunta en la comunidad de Power Bi ?
Si las entradas registradas son incorrectas yo las limpiaría en Power Query, pero si las necesitas para otros análisis las puedes eliminar con Dax. Saludos!
/Ruth
I get a syntax error reported when using EARLIER inside the FILTER. "EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
But the FILTER is creating the row context...
.. baffled.
Oh. It's a calc column, not a measure. Obviously.
As you were.
You found it :)
/Ruth
Hi Ruth...when I downloaded this file, it doesn't have the solution attached...when I tried to do it, it failed...Plz help!!
It doesnt ? I Will check on Monday.
/Ruth
yes...when I try to create the combined formula into a measure, the earlier function is giving a problem stating not able to find the Subscriptions[SubscriptionCreated].
Steven Even I updated the file with the calculations shown in the video. please download it again.
/Ruth
@@CurbalEN Thank you very much but something weird is going on. So I went a head and copy/paste the measure, it complains about the earlier function still but the newly downloaded file works fine. This is very weird!!
Steven Even are you doing a measure or a calculated column?
/Ruth
I just watched the Guy in a Cube video with Marco Russo on debugging using variables. He recommends going even further with the variables, and in that vein I've done it like this.
Pretty neat.
DaysSubscribed =
VAR UserFilter = ALLEXCEPT(Subscriptions,Subscriptions[UserAccountId])
VAR UserAccountCreated = Subscriptions[AccountCreated]
VAR UserCurrentSubDate = Subscriptions[SubscriptionCreated]
VAR FirstSub =
CALCULATE(
MIN(Subscriptions[SubscriptionCreated]),
UserFilter
)
VAR PreviousSubDate =
CALCULATE(
MAX(Subscriptions[SubscriptionCreated]),
FILTER(
UserFilter,
Subscriptions[SubscriptionCreated] < UserCurrentSubDate
)
)
VAR DayDiff =
IF(UserCurrentSubDate = FirstSub,
DATEDIFF(UserAccountCreated,UserCurrentSubDate,DAY),
DATEDIFF(PreviousSubDate, UserCurrentSubDate,DAY)
)
RETURN
DayDiff
Excellent and thanks for sharing :)
/Ruth