DAX Fridays! #17: DATEDIFF
Vložit
- čas přidán 15. 12. 2016
- In today's video, I will show you how to calculate the difference between two dates using DATEDIFF.
I also show you how to overcome the issue when the start date is larger than the end date.
PREVIOUS VIDEO: • DAX Fridays! #15: SWITCH
NEXT VIDEO: -
Link to demo file: gofile.me/2kEOD/G4FzzQLvV
Link to Northwind dataset: • Northwind data source:...
Link to DAX Switch: • DAX Fridays! #15: SWITCH
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 #SUBSCRIBE
► Linkedin: goo.gl/3VW6Ky
Muy claro como siempre Ruth, Gracias por compartir!!! tienes el mejor canal de formación en DAX y PowerBI...
+Norberto Vera Reatiga Gracias, vosotros me lo ponéis fácil :)
/Ruth
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.
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.
Love your videos
The best power bi/Dax around
+PATRICK NYAMU Thanks Patrick, what a great compliment!
Thanks for sharing it :)
/Ruth
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?
thanks for this and the clever workaround too!
Yey!! Glad it was useful :)
/Ruth
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.
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.
Sure thing! And thanks for the feedback :)
/Ruth
You are the BEST, XOXO
Thanks!
Hi Ruth, can i use datediff with 2 dates both in different tables?
Excellent Content! Que viva ASTURIAS!!
Puxa Asturies!! 🤟🤟
Can i create a percentage between the two dates - order date and ship date? If user wants how can we show this?
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.
Hi Farman, could you post your question in the power bi community? It is a better place to get support.
Thanks !
/Ruth
@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
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.
Excellent eloquent explanation as to how to redress start date, greater than end date.
+Cloud Hound Thanks Christopher! A bit annoying though that the limitation exists...
/Ruth
I've often found IT throws issues and work arounds' are the only way forward despite being somewhat questionable. Have a fabulous week.
+Cloud Hound
I guess it helps keep our brains fit! ;)
Same to you Christopher.
/Ruth
How very true.
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
Hi Marlon,
To be able to use time intelligence functions you need continuous dates.
Does your fact table have that?
/Ruth
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
Hi Ruth any feedback on this thanks Marlon
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
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
+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. CZcams is not optimal for this.
/Ruth
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 ?
+kumar ashwarya Hi Kumar,
In the power bi community you just attach the file or paste it into the thread.
/Ruth
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
For support, please contact the Power bi community, thanks!
/Ruth
SOS - Do you have any video for calculate working days between 2 dates?
+Lebon Tan Hi, no I don't buy I will make one :)
/Ruth
TQ! Look forward, that will be life saving. You have a good day!
+Lebon Tan Same to you! You want to calculate it with DAX right?
/Ruth
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
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
+Cristian Dominguez Hi Christian!!
Have you checked the NETWORKINGDAYS and TIME Dax Fridays videos?
You can find them here:
Https://Curbal.com/blogglossary
/Ruth
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!
Lo he conseguido!! Ya no hace falta que me respondas 😊. Qué guaaayyyy, cuánto aprendo con tus vídeos!
Why do I get errors?
Revision :D
Mmmm what do you mean :)
como puedo usar la funcion dias.lab, en DAX?
+Roberth rodriguez chacon Hola Robert,
Es NETWORKDAYS la función que buscas?
/Ruth
Si en excel es NETWORKDAYS , en DAX cual seria?
si, en excel es NETWORKDAYS pero en DAX cual es la funcion?
+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