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!
@sedwards2207
Жыл бұрын
Great idea, Chris. I’ll give that a try.
@nileshpancholi74
2 жыл бұрын
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.
@HavensConsulting
2 жыл бұрын
Happy it helped!
@aleksandrmolchanov827
2 жыл бұрын
Brilliantly explained! Thank you for your elaboration and for being on the point! A+++
@benharris8073
2 жыл бұрын
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!
@gpkennedy34
Жыл бұрын
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!
@HavensConsulting
Жыл бұрын
Glad it helped!
@BenShay
Жыл бұрын
Thank you for saving me more hours of frustration.
@HavensConsulting
Жыл бұрын
Happy to help
@ValeriaFassio
11 ай бұрын
brilliant!!! you explained really well, I finally understood the problem! Thanks!!!
@HavensConsulting
11 ай бұрын
Glad it helped!
@briang43
3 жыл бұрын
No joke... I love you man! THANK YOU. This video just helped me keep my sanity with this project (for now)
@HavensConsulting
3 жыл бұрын
Glad I could help!
@miqueldespuig
3 жыл бұрын
I went crazy with one report until figured it out! Thanks Reid!
@HavensConsulting
3 жыл бұрын
Right? It’s a really nuanced issue!
@chandrasekhar1883
2 жыл бұрын
yes it's very help full video. made my day. Thank you so much
@thedatastoryteller5692
2 жыл бұрын
thanks so much ! the last trick solved my issue! You are awesome!
@michelmolongo
Жыл бұрын
This is fantastic ! You made my day. Cheers
@AgulloBernat
3 жыл бұрын
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 😀
@switchfire
8 ай бұрын
Thank you SO much. This saved me!
@rajivjani8594
2 жыл бұрын
Many thanks for sharing this information! It helps!
@marcovargas8086
2 жыл бұрын
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.
@angelicavazquezhuizar752
Жыл бұрын
Wow! I was also getting errors, and I was only able to make it work thanks to you comment :)
@MultiMortal
Жыл бұрын
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?
@HavensConsulting
Жыл бұрын
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
@vitorribas1910
Жыл бұрын
This solved my problem, thank you!
@HavensConsulting
Жыл бұрын
Happy to help!
@williamleveson-gower5088
2 жыл бұрын
Thank you, thank you, thank you !!!!.
@komy4
2 жыл бұрын
You saved my soul
@HavensConsulting
2 жыл бұрын
The best response, you're welcome!
@pabeader1941
3 жыл бұрын
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
3 жыл бұрын
Thank you! I wished PBID made it more clear about this issue, which is why I made this video.
@norpriest521
3 жыл бұрын
@@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? 😂
@HavensConsulting
3 жыл бұрын
@@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. :\
@timothywaldman4945
3 жыл бұрын
This is great, thank you!
@monishkumar3450
Жыл бұрын
You saved me. Thank you !!
@HavensConsulting
Жыл бұрын
You're welcome!
@dinaalmajaly5169
Жыл бұрын
Life Saver
@MrBobbybuche
Жыл бұрын
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.
@HavensConsulting
Жыл бұрын
Great alternative! Yes if you can't apply a date extract in PQ, then a DAX column works just fine :)
@bronaghquinn2081
Жыл бұрын
Great video, saved me a lot of time - thank you
@richardpierce919
4 ай бұрын
Hi, if I have power query from t-sql that brings through datetime with zero seconds should I convert to date?
@HavensConsulting
4 ай бұрын
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
@joshstone313
6 ай бұрын
OH MY GOSH - Thank you so much for this! Many, many hours later. As a Power BI beginner, this was so not obvious.
@PDOT1990
2 ай бұрын
You are a God send!!! The way I have been stuck on this for 4 hours going back and fourth. Thank you so much.
@HavensConsulting
2 ай бұрын
Glad I could help!
@Dude276175
Жыл бұрын
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!!!!😀
@yichen5142
Жыл бұрын
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.
@jimmyxrs
11 ай бұрын
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! :)
@catherinevisitacion3531
2 жыл бұрын
Thank you! Really helped me with my work! More blessing to you!!!
@BaldNinjaChronicles
Жыл бұрын
I just wanted to say a huge thank you I was struggling with this a found it very difficult to find an answer.
@HavensConsulting
Жыл бұрын
So happy it helped! Honestly my favorite video I recorded just because of how it helps people
@travisgordon4750
Жыл бұрын
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!
@Yx9000
3 жыл бұрын
Thank you it's work
@oscarelworthy
Ай бұрын
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?
@HavensConsulting
Ай бұрын
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
@oscarelworthy
Ай бұрын
@@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!
@HavensConsulting
Ай бұрын
@@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 :)
@leakemi
2 жыл бұрын
Awesome!! 👏👏👏👏👏👏
@gayathrivasudev6116
7 ай бұрын
Thanks a lot ... 🙏
@sabrinatorres4108
2 ай бұрын
Hope this works for 😢
@joelgamal1684
2 жыл бұрын
Really THANK YOU so much, this helped me a lot
@ChrisSmithFW
2 жыл бұрын
Fantastic video! Have wracked my brain all week on this issue! Much appreciated!
@matsjohnsson1096
3 жыл бұрын
Thanks - was thinking I was an idiot trying to get it to work until I saw this video - thanks alot
@EricHartwigExcelConsulting
3 жыл бұрын
Oh Wow! You break this down into a great explanation and example! Thank you so much for this information!
@JL_Klean
Жыл бұрын
Thanks so much!! This was just what I was looking for.
@HavensConsulting
Жыл бұрын
You're very welcome! I'm glad you found it helpful.
@sanaths4371
Жыл бұрын
Thank you
@nicemovies99
Жыл бұрын
Thank you so much! Finally solved my issue!
@HavensConsulting
Жыл бұрын
You're welcome!
@gborka
2 жыл бұрын
Great tip
@ZZstarX2855
2 жыл бұрын
Thank you for this great explanation!
@mr.somebody6368
3 жыл бұрын
This saved me so much time, I wish I saw this earlier. Thanks a lot!!!
@bibliosophist
2 жыл бұрын
That was very helpful. Thanks.
@HavensConsulting
2 жыл бұрын
Glad it was helpful!
@usmansharifshaik1187
3 жыл бұрын
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
@HavensConsulting
2 жыл бұрын
Glad I could help!
@paulomodestoo
Жыл бұрын
Thanks bro! This video saved me!
@HavensConsulting
Жыл бұрын
Glad it helped!
@jakeryan56
2 жыл бұрын
You are an absolute lifesaver!!!!
@jakoblauridsen4347
3 жыл бұрын
amazing you just saved my sanity!
@ruesaintdenis
3 жыл бұрын
Very grateful for this, thank you!
@gabrielcoral2858
2 жыл бұрын
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
@HavensConsulting
2 жыл бұрын
I'm glad I could help! It's been a major pain for so many people! 😅
@kasperlj
3 жыл бұрын
Thank you - saved me so much time!
@Festa_no_IML
3 жыл бұрын
saved me a lot of thought! thanks!
@BigO_Notation
2 жыл бұрын
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
@HavensConsulting
2 жыл бұрын
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.
@BigO_Notation
2 жыл бұрын
@@HavensConsulting I get the Max Date. Its working OMG. Thank you so much sir.
@havenscr
2 жыл бұрын
@@BigO_Notation glad to hear it! I'd check out my other calendar videos too! Plenty of good tips on making quality calendar tables
@HavensConsulting
2 жыл бұрын
kzitem.info/news/bejne/opqCqo2loX6WfZg
@BigO_Notation
2 жыл бұрын
@@havenscr definitely, I have been looking at your contents and will check out the rest. Thank you
@gabrielmorais7312
3 жыл бұрын
Wow, this is great. Thanks Reid!
@debasreetripathy5962
3 жыл бұрын
Very helpful 🤠👍👍👍
@JoeAverage2006
3 жыл бұрын
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?
@HavensConsulting
3 жыл бұрын
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.
@JoeAverage2006
3 жыл бұрын
@@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
3 жыл бұрын
@@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
@JoeAverage2006
3 жыл бұрын
@@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.
@havenscr
3 жыл бұрын
@@JoeAverage2006 sure thing! My video on issues with date time from earlier this year talks about this as well 🙂
@rollyv1687
3 жыл бұрын
Now this make sense
@mnowako
3 жыл бұрын
Good one 👍. Thank you.
@LegoTP
2 жыл бұрын
Straight to the point, you saved my report ;) thanks!
Пікірлер: 106