Thanks! This has been driving me nuts for years. I kinda understood what was going on, but your explanation put it in front of me where I could really see it.
@@HavensConsulting I sat through this pain for about a day and tried to figure out what I did wrong and implemented so many solutions and it still didn't work until 15 mins ago when I accidentally found a comment in some power bi community website. Some guy said "Open it with Power Query" so I tried it and I was like dying. I mean how could someone figure this out? In fact, Microsoft should put this issue in the first page of power bi training material saying that "DO NOT CHANGE DATE TIME DATA TYPE ON POWER BI BECAUSE IT'S USELESS AS HELL AND USE POWER QUERY INSTEAD, FOR THE LOVE OF GOD!!!" I think this is the worst bug i ever found in power bi. Have you ever encountered a worse bug than this one? 😂
@@norpriest521 Yeah sadly I agree. I've given candid feedback to the Power BI team that this needs to be updated. Since it's the ONLY type conversion you do that DOESN'T actually change the type. :\
You are a hero! I bashed my head against this for an hour this morning before figuring out what question I needed to ask, which was answered right here! Thank you!
Experienced that myself! I could not believe what was happening so i created a calculated column in dax with the year month and day of the column and worked! Now I understand why 😀
What a great video that just saved me from hours of pulling my hair out. I completed the changes you suggest in the video and a model I created just all fell into line. Can't thank you enough.
I wish I would have discovered this a week ago! I have been trying to join a Date field to a Date/Time field and had no idea why nothing was work. Thanks!!!!😀
Great video! I've found there are many nuances to making the most of time intelligence. Personally, I'm not fond of joining on a date - for this very reason. I've found that adding a column in Power Query with this simple formula (=Number.From()) converts my date column into an integer value. I use this column in my Date Table as primary key to join to other tables that have dates. The tables I join have a date foreign key column that uses the same formula. Now I'm joining on integer values, which is my personal preference. Keep up the great work!
super helpful and well explained! The ref article is a treasure! Note, if any of the datetime columns contains a timestamp (e.g.yyyy,mm,dd,hh,nn,ss) then the other table should have hh,nn,ss in order to create a relationship.
Good question. If you have a fact table with both Date and Time dimensions it's recommended to have seperate dimension tables for each. Otherwise you'd need one row for every second between your start and stop date. That's 36 million rows per year! Here's a good article talking about splitting your date and time tables. radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model
Definitely, I'd recommend converting any DateTimes to Date in Power Query before importing, if you don't need time, else best practice is having a separate Date and time Dim Table
Are you getting the min and max dates from your fact table or using the CALENDARAUTO function? If you make your calendar table using: CALENDAR( MIN(Fact[Date]), MAX(Fact[Date]) ) Then your date range in the calendar table will match your fact table.
I realize that this is a bit older video, and that this information may already be known, but thought I would share anyway. In my case, the data sets I am working with are somewhat large and I am, for reasons I won't go into here, having to pull it over VPN as well, so modifying my Power Query was not something I wanted to do unless I had to. It occurred to me that there might be another option, which led me to an alternative work-around for this issue. If you create a new column with DAX (e.g. Created = [MyDateTimeColumn].[Date]) and format it as Date with the ShortDate format, you can use the date table without breaking the date field, or having to add tons of items to your date table to match granularity.
When we generate dates in power BI dax, such as "date(2024,8,30)", it says these will return the date in datetime format. Do you know which of the three datetime formats these are loaded in?
Are you referring to the actual datetime format? Not the data type? If so I think it defaults to the locale of your machine, since places like US, Europe, Asia, etc. all have different standard formats
@@HavensConsulting In powerquery, dates can be either date, datetime, or datetimetimezone. Then once the values are in the model, you can only change their visible format. So my question is, which datatype from powerquery would match with dates generated entirely within the model (such as a calendar or date() dax function). I'm having issues comparing dates from powerquery with dates generated within the model as above. Sorry for any confusion about format/datatype. thanks for the quick reply!
@@oscarelworthy the model itself only has datetime, the rest is "formatting", so I'd recommend converting in PQ to date only first, for your fact dates to then correctly key to DIM - Date on your calendar table :)
Is it possible to keep the imported column as datetime, and then have a calculated column that uses only the date part, and then join on the calculated column?
Absolutely! You could add that column either through Power Query, or through DAX. Personally I recommend PQ because it compresses the data a bit better during the refresh/import process.
@@HavensConsulting So if I want to keep the time part of the datetime data, but still want to join to a calendar date field - would you say the best option is to create an extra date-only calculated field in PQ and use that to join back to the calendar table? My only concern is that I have quite a few dates in my data, so would end up with a lot of duplicated date only columns. Thanks for the reply as well!
@@havenscr Thank you! I'll start creating those date only columns to join on. It is interesting, there are many calendar tutorials out there but almost no one mentions this.
THANK YOU so much for this. I had been pounding my head against my desk for the last 2-hours trying to figure out what the problem was. Bravo!
me right now... oh my goodness, that totally worked. 😭😆
Thanks! This has been driving me nuts for years. I kinda understood what was going on, but your explanation put it in front of me where I could really see it.
Thank you! I wished PBID made it more clear about this issue, which is why I made this video.
@@HavensConsulting
I sat through this pain for about a day and tried to figure out what I did wrong and implemented so many solutions and it still didn't work until 15 mins ago when I accidentally found a comment in some power bi community website.
Some guy said "Open it with Power Query" so I tried it and I was like dying.
I mean how could someone figure this out?
In fact, Microsoft should put this issue in the first page of power bi training material saying that "DO NOT CHANGE DATE TIME DATA TYPE ON POWER BI BECAUSE IT'S USELESS AS HELL AND USE POWER QUERY INSTEAD, FOR THE LOVE OF GOD!!!"
I think this is the worst bug i ever found in power bi.
Have you ever encountered a worse bug than this one? 😂
@@norpriest521 Yeah sadly I agree. I've given candid feedback to the Power BI team that this needs to be updated. Since it's the ONLY type conversion you do that DOESN'T actually change the type. :\
You are a hero! I bashed my head against this for an hour this morning before figuring out what question I needed to ask, which was answered right here! Thank you!
Experienced that myself! I could not believe what was happening so i created a calculated column in dax with the year month and day of the column and worked! Now I understand why 😀
You are a God send!!! The way I have been stuck on this for 4 hours going back and fourth. Thank you so much.
Glad I could help!
OH MY GOSH - Thank you so much for this! Many, many hours later. As a Power BI beginner, this was so not obvious.
What a great video that just saved me from hours of pulling my hair out. I completed the changes you suggest in the video and a model I created just all fell into line. Can't thank you enough.
Happy it helped!
I wish I would have discovered this a week ago! I have been trying to join a Date field to a Date/Time field and had no idea why nothing was work. Thanks!!!!😀
Great video! I've found there are many nuances to making the most of time intelligence. Personally, I'm not fond of joining on a date - for this very reason. I've found that adding a column in Power Query with this simple formula (=Number.From()) converts my date column into an integer value. I use this column in my Date Table as primary key to join to other tables that have dates. The tables I join have a date foreign key column that uses the same formula. Now I'm joining on integer values, which is my personal preference. Keep up the great work!
Great idea, Chris. I’ll give that a try.
OMG !!
For the last 3 hours, I am searching for this information. Man you saved me
I can’t thank you enough. !!!!
love from India
Glad I could help!
I lovvvvvveeeeeeeee you, i've been literally looking for this a whole week in powerbi forums and i got nothing.
You got a new subscriber
I'm glad I could help! It's been a major pain for so many people! 😅
Your are a lifesaver! Was driving me mad and this was the exact issue i was having and this solution worked a treat. Thank You! :)
Thank you so much. I've been battling with this issue for the past 3 days and could not figure out what I was doing wrong. Cheers!
Glad it helped!
Thank you! Really helped me with my work! More blessing to you!!!
Brilliantly explained! Thank you for your elaboration and for being on the point! A+++
Fantastic video! Have wracked my brain all week on this issue! Much appreciated!
brilliant!!! you explained really well, I finally understood the problem! Thanks!!!
Glad it helped!
Thanks - was thinking I was an idiot trying to get it to work until I saw this video - thanks alot
Thank you for the video! I was getting frustrated with the cross filtering not functioning properly :D
No joke... I love you man! THANK YOU. This video just helped me keep my sanity with this project (for now)
Glad I could help!
Not sure as to why, but using the locale option rather than just changing to date, which would result in errors, helped me achieve this.
Wow! I was also getting errors, and I was only able to make it work thanks to you comment :)
super helpful and well explained! The ref article is a treasure!
Note, if any of the datetime columns contains a timestamp (e.g.yyyy,mm,dd,hh,nn,ss) then the other table should have hh,nn,ss in order to create a relationship.
I went crazy with one report until figured it out! Thanks Reid!
Right? It’s a really nuanced issue!
Oh Wow! You break this down into a great explanation and example! Thank you so much for this information!
Excelent content. However, I need the other way around and show Date/Time. How can I make Power BI Desktop understand the same Date/Time format?
Good question. If you have a fact table with both Date and Time dimensions it's recommended to have seperate dimension tables for each. Otherwise you'd need one row for every second between your start and stop date. That's 36 million rows per year! Here's a good article talking about splitting your date and time tables. radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model
This saved me so much time, I wish I saw this earlier. Thanks a lot!!!
You are an absolute lifesaver!!!!
Great video, saved me a lot of time - thank you
Hi, if I have power query from t-sql that brings through datetime with zero seconds should I convert to date?
Definitely, I'd recommend converting any DateTimes to Date in Power Query before importing, if you don't need time, else best practice is having a separate Date and time Dim Table
This is fantastic ! You made my day. Cheers
Thank you for saving me more hours of frustration.
Happy to help
Many thanks for sharing this information! It helps!
Your help is appreciated, on my calendar date I see extra date compared to my fact table. Why is it not showing the same range? Thank you
Are you getting the min and max dates from your fact table or using the CALENDARAUTO function? If you make your calendar table using:
CALENDAR( MIN(Fact[Date]), MAX(Fact[Date]) )
Then your date range in the calendar table will match your fact table.
@@HavensConsulting I get the Max Date. Its working OMG. Thank you so much sir.
@@BigO_Notation glad to hear it! I'd check out my other calendar videos too! Plenty of good tips on making quality calendar tables
th-cam.com/video/AdLDYohLeJc/w-d-xo.html
@@havenscr definitely, I have been looking at your contents and will check out the rest. Thank you
Thanks so much!! This was just what I was looking for.
You're very welcome! I'm glad you found it helpful.
I just wanted to say a huge thank you I was struggling with this a found it very difficult to find an answer.
So happy it helped! Honestly my favorite video I recorded just because of how it helps people
yes it's very help full video. made my day. Thank you so much
Wow, this is great. Thanks Reid!
thanks so much ! the last trick solved my issue! You are awesome!
I realize that this is a bit older video, and that this information may already be known, but thought I would share anyway. In my case, the data sets I am working with are somewhat large and I am, for reasons I won't go into here, having to pull it over VPN as well, so modifying my Power Query was not something I wanted to do unless I had to. It occurred to me that there might be another option, which led me to an alternative work-around for this issue. If you create a new column with DAX (e.g. Created = [MyDateTimeColumn].[Date]) and format it as Date with the ShortDate format, you can use the date table without breaking the date field, or having to add tons of items to your date table to match granularity.
Great alternative! Yes if you can't apply a date extract in PQ, then a DAX column works just fine :)
When we generate dates in power BI dax, such as "date(2024,8,30)", it says these will return the date in datetime format. Do you know which of the three datetime formats these are loaded in?
Are you referring to the actual datetime format? Not the data type? If so I think it defaults to the locale of your machine, since places like US, Europe, Asia, etc. all have different standard formats
@@HavensConsulting In powerquery, dates can be either date, datetime, or datetimetimezone. Then once the values are in the model, you can only change their visible format.
So my question is, which datatype from powerquery would match with dates generated entirely within the model (such as a calendar or date() dax function).
I'm having issues comparing dates from powerquery with dates generated within the model as above.
Sorry for any confusion about format/datatype.
thanks for the quick reply!
@@oscarelworthy the model itself only has datetime, the rest is "formatting", so I'd recommend converting in PQ to date only first, for your fact dates to then correctly key to DIM - Date on your calendar table :)
Straight to the point, you saved my report ;) thanks!
Glad it helped!
Is it possible to keep the imported column as datetime, and then have a calculated column that uses only the date part, and then join on the calculated column?
Absolutely! You could add that column either through Power Query, or through DAX. Personally I recommend PQ because it compresses the data a bit better during the refresh/import process.
@@HavensConsulting So if I want to keep the time part of the datetime data, but still want to join to a calendar date field - would you say the best option is to create an extra date-only calculated field in PQ and use that to join back to the calendar table? My only concern is that I have quite a few dates in my data, so would end up with a lot of duplicated date only columns. Thanks for the reply as well!
@@JoeAverage2006 that’s correct. The only way to join to a date only calendar table is you’d need a date key that is also date only
@@havenscr Thank you! I'll start creating those date only columns to join on. It is interesting, there are many calendar tutorials out there but almost no one mentions this.
@@JoeAverage2006 sure thing! My video on issues with date time from earlier this year talks about this as well 🙂
amazing you just saved my sanity!
Thank you for this great explanation!
Thank you SO much. This saved me!
Thank you so much! Finally solved my issue!
You're welcome!
Thank you - saved me so much time!
You saved me. Thank you !!
You're welcome!
Really THANK YOU so much, this helped me a lot
This solved my problem, thank you!
Happy to help!
Awesome!! 👏👏👏👏👏👏
Very grateful for this, thank you!
That was very helpful. Thanks.
Glad it was helpful!
saved me a lot of thought! thanks!
This is great, thank you!
Thank you, thank you, thank you !!!!.
Very helpful 🤠👍👍👍
Thanks bro! This video saved me!
Glad it helped!
Good one 👍. Thank you.
You saved my soul
The best response, you're welcome!
Thanks a lot ... 🙏
Great tip
Thanks so much.
You're welcome!
Thank you so much
You're most welcome
Thank you it's work
Now this make sense
Thank you
Hope this works for 😢
Life Saver