DAX Fridays! #17: DATEDIFF

[@NorbertoVeraReatigaNVR]

Muy claro como siempre Ruth, Gracias por compartir!!! tienes el mejor canal de formación en DAX y PowerBI...

[@CurbalEN]

+Norberto Vera Reatiga Gracias, vosotros me lo ponéis fácil :) /Ruth

[@GoodlyChandeep]

@11:22 - In the 3rd Row your order date is 29 July 1996 and your shipped date is 6 Aug 1996. The value that returns from the Datediff (months) function is 1. How is that possible? Since one full month hasn't crossed between two dates

[@TommyJgreene]

Great videos Ruth. I am hoping you will do a follow up video on using date diff as a measure rather than a calculated column. Calculating dynamically based on date criteria (ie max(date), by using your examples (values or lastdate function) around11:00 of this video.

[@CurbalEN]

Sure thing! And thanks for the feedback :) /Ruth

[@kunwarmaurya2025]

Thank you, Ruth. I'm not getting errors if the start date is greater than the end date. It automatically gives the difference in Minus.

[@thyang3999]

Thank you so much for your sharing. What if we also need to display to whether the Delivery sooner or later than the Required date?

[@patricknyamu6110]

Love your videos The best power bi/Dax around

[@CurbalEN]

+PATRICK NYAMU Thanks Patrick, what a great compliment! Thanks for sharing it :) /Ruth

[@drgrd2128]

Can i create a percentage between the two dates - order date and ship date? If user wants how can we show this?

[@martinarichiger8150]

Thank you very much Ruth for the great explanation. Was looking exaxtly for DATEDIFF but any recommendation when the two date are in different tables? Your help is very appreciated.

[@patticmcconnell]

Hi and I enjoy your videos very much! Is there a way to count the days & remove weekends & vacation days? Appreciate your help with this one.

[@juanignacioXI]

Hi Ruth, can i use datediff with 2 dates both in different tables?

[@farmanalishah8836]

Hi Ruth, Can you please help , i have two different dates, "Triage Date/Time (dd/mm/yyyy hh:mi)" and "Registration Date/Time (dd/mm/yyyy hh:mi)" i have to to find how much time it took to get registration? Only need to find time.

[@CurbalEN]

Hi Farman, could you post your question in the power bi community? It is a better place to get support. Thanks ! /Ruth

[@marlonhalliday3274]

Hi how do you do a filter with a list of dates based on the dates on a fact table and not from the date table. thanks

[@CurbalEN]

Hi Marlon, To be able to use time intelligence functions you need continuous dates. Does your fact table have that? /Ruth

[@marlonhalliday3274]

Hi Ruth, my date table has continuous dates, say from 2007 to 2025 and the fact table will say has dates from 2014 to 2018. I want the slicer to show only the years from 2014 to 2018 as per the fact table. Thanks

[@marlonhalliday3274]

Hi Ruth any feedback on this thanks Marlon

[@CurbalEN]

Maybe this will help you, if it doesn’t, please comment on that thread: community.powerbi.com/t5/Desktop/Visual-level-filtering-for-slicers/td-p/21323 /Ruth

[@tomaszledzki7747]

Is there a way for DATEDIFF to show decimal places? ie between 1.01.2021 and 15.02.2021 DATEDIFF returns 1, how to make it show 1.5? I'm thinking about let's say 1mothn deadline. Tasks starts on 15th February, is finished on the 20th March. DATEDIFF shows 1 month, but in fact the task wasn't finished on time.

[@lebontan9062]

SOS - Do you have any video for calculate working days between 2 dates?

[@CurbalEN]

+Lebon Tan Hi, no I don't buy I will make one :) /Ruth

[@lebontan9062]

TQ! Look forward, that will be life saving. You have a good day!

[@CurbalEN]

+Lebon Tan Same to you! You want to calculate it with DAX right? /Ruth

[@karljolivet5991]

If it would be possible to explain how to create a measure calculating the sales amount difference between current day and previous day that would help me so much

[@kumarashwarya2185]

Hello, I was calculating the time difference between two dates( the dates were in the format "DD/MM/YYYY/HH/MM/SS) and using your method I still wasn't able to calculate the difference in time. The dates were starting time and end time of different resources that are being used by the students. Please help, I would really appreciate Thank You, Kumar Ashwarya

[@CurbalEN]

+kumar ashwarya Difficult to say without seeing the data. Have you seen this; community.powerbi.com/t5/Desktop/Calculate-difference-between-two-date-time-values/td-p/71841 The best way is posting your question in the power bi community with sample data. KZbin is not optimal for this. /Ruth

[@kumarashwarya2185]

I think you have done the same thing and as I said that I tried doing what you have done. how can I send show a sample data from my data set ?

[@CurbalEN]

+kumar ashwarya Hi Kumar, In the power bi community you just attach the file or paste it into the thread. /Ruth

[@bernardolea]

Why do I get errors?

[@rorochic26]

como puedo usar la funcion dias.lab, en DAX?

[@CurbalEN]

+Roberth rodriguez chacon Hola Robert, Es NETWORKDAYS la función que buscas? /Ruth

[@rorochic26]

Si en excel es NETWORKDAYS , en DAX cual seria?

[@rorochic26]

si, en excel es NETWORKDAYS pero en DAX cual es la funcion?

[@CurbalEN]

+Roberth rodriguez chacon El equivalente no existe en DAX, pero este artículo describe como lo puedes calcular a mano: powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/ Haré un vídeo sobre esto ;) /Ruth

[@J4RMAMS]

Excellent Content! Que viva ASTURIAS!!

[@CurbalEN]

Puxa Asturies!! 🤟🤟

[@1989Bismillah]

thanks for this and the clever workaround too!

[@CurbalEN]

Yey!! Glad it was useful :) /Ruth

[@CloudhoundCoUk]

Excellent eloquent explanation as to how to redress start date, greater than end date.

[@CurbalEN]

+Cloud Hound Thanks Christopher! A bit annoying though that the limitation exists... /Ruth

[@CloudhoundCoUk]

I've often found IT throws issues and work arounds' are the only way forward despite being somewhat questionable. Have a fabulous week.

[@CurbalEN]

+Cloud Hound I guess it helps keep our brains fit! ;) Same to you Christopher. /Ruth

[@CloudhoundCoUk]

How very true.

[@Hamid-tb8kc]

You are the BEST, XOXO

[@CurbalEN]

Thanks!

[@alejandra1888]

gracias!! muy útil, tengo una pregunta, a ver si me puedes redirigir a otro vídeo :). ¿Cómo introducir en el switch un condicional como "si la shipped date está vacía, DATEDIFF(required date, CURRENT DATE, day)"? Millones de gracias!

[@alejandra1888]

Lo he conseguido!! Ya no hace falta que me respondas 😊. Qué guaaayyyy, cuánto aprendo con tus vídeos!

[@christianfcbmx]

Hi!!!long time teacher!...I wanted to ask you something related to an advanced dax for counting minutes between start time and end time avoiding weekends and holidays and... considering hours from 9am to 17pm? I did it in excel but no much clue to do it in power BI..any advice would be really well recieved...Greetings and thanks again for your fabulous channel!!!! excel example that is working pretty good is:(=SI(O($B$2

[@CurbalEN]

+Cristian Dominguez Hi Christian!! Have you checked the NETWORKINGDAYS and TIME Dax Fridays videos? You can find them here: Https://Curbal.com/blogglossary /Ruth

[@muhammadmuavia8024]

i have a column of plan finish date now its already due date so i wana make new column of age that just show the age of that by following below formula According to risk warning over due days= plan finish date-today or a current date over due days between 28-21= age with -3 to -4 over due days between 21-14= age with -2 to -3 over due days between 14-07= age with -2 to -1 over due days between 07-0= age with -1 to 0 how i can write its query or calculation what i wat i want to need four columns of differenrt weeks ages as shown below secondly i need total age of a week that will be due from plan date for example task or a plan due date is 22/05/2018 and today date is 29/10/2018 so how i can calculate total weeks inthis duration with the help of formula

[@CurbalEN]

For support, please contact the Power bi community, thanks! /Ruth

[@rrrprogram8667]

Revision :D

[@CurbalEN]

Mmmm what do you mean :)