DATE TABLE for Power BI using Power Query

Awesome! Here I thought I had to create a new Date Table for every range I wanted to look at! Thanks for helping me create my first Date Table!

Bas, Thanks. I'm discovering your video, its brilliant. I nevertheless have a problem: I cannot sort the column Month by the column MonthNo. (it works for quarter). PBI says that there can't be more than one value in MonthNo for the same value in Month. Many Thanks

This is the best explanation I've come across on how to do this.

Is there any benefit in doing this wit PQ in stead of Dax? Usually I would set the start and end date based on the dataset max and min dates to get the correct date range.

Just use =CALENDARAUTO

you lost me at 18mins13sec but from some further googling I see you must have right clicked to create the hierarchy. I appreciate all the videos but us newbies sometimes need a little more info. I agree with another user - can you make your mouse cursor more visible. Thanks

too intimate. dislike.

Thanks for the video. One small suggestion is that, it would be much easier to follow if you could make your mouse cursor more visible. I had a hard time to see where did you click sometimes.

I spent at least one hour to view and practice from what I learnt from this video, at beginning I copied the codes from the end of session into advanced PQ editor, it didn't work, so I do it again from beginning, also learnt the trick to bulk update un-summaries columns 😇

Thanks, the PQ method was what I was looking for

Many Thanks!
this is a beneficial video !!

I couldn't use analytics trend or forecast in my visualization 'ex. line chart" when I use the calendar table date column that related to data table "ex. sales"

The function works great.. but when i add it to my data i get errors which makes the query impossible to load. I replaced errors and nulls with zeros. Still same. Im not able to use the function. My dates are dates type and i have no negative results 😢. Help!

Enlarge ur desktop screen

for inserting new step do you use a keyboard shortcut ?

Whoa, but what about the range of dates in my sql queries? My start dates aren’t aligned eeek!

From where and which data you used ?

Your videos are awesome. You are very clear and your voice remember me to jhon (the father in this is us). Its very relaxing and nostalgic i dont know

Solid tutorial Bas - awesome

Thank you! Very helpful!

Would help to see other areas of the screen. Lost when you started creating the list of dates after commenting the variables

Can you please make a video on how to slice between calender year and financial year

I've just discovered this Channel, i realize I have at least 2 years of knowledge to catch up. And with your vids which are wisely structured and dense I'll catch up fast. Big thanks !

Very informative. Thanks!

Thank you so much for taking the time to walk through this date creation. I have seem many different videos and yours is the best, easiest to understand by far and you walk through every possible issue which I just love and very much appreciate!!!

Why this video got only 1k likes... it should be 1million likes.. u deserves it... 😃😃.. nice explanation.. "New subscriber added!!"

Thank you! It was great, and easy to follow tutorial.

Hi Bas, awesome video can my Quarters be different. For example, if my Start Date is in July-Sep for Quarter 1, Oct- Dec= Quarter 2 ?

Great lesson. Tank you.

Hello! Thank you for the video! In your relationships will this only work for order date? If so, how do you make the connection work for your other dates like Due Date or Ship Date?

I didn't fully understand the reasons for hiding some columns and for disabling the date learning options

Am I correct to assume the automated date tables also don't allow you to use Dax code that let you compare month to previous month? Only after applying a custom table did such calculations work. But I'm not 100% on whether this is due to the automated date tables or something else.

Helps a lot. Thanks Dear

This is the best explanation I've come across on how to do this. I loved how you showed the actual steps so that I could follow along and explained what each step was doing. I'm not a coder, but I could follow this. I like how you went back at the end and showed how it appeared in the data field.

Good afternoon, Bas (and thank you for sharing you knowledge on the web). Your week_id-column really BLEW MY MIND and open SO many new doors for me regarding things that is possible in BI. I can't understand why no one else (as far as I have found) has done this before! Since this video was uploaded, do you have any new tips/tricks regarding creating this (holy) date table? :) Best regards, Norway!

can someone paste the code for making the date table so we dont have to write from scratch? thx

Thanks for this video. It was very helpful!

Nice Vid! How do you add Fiscal Year/Quarter as new column?

Hello. Thank you for the video. I have a question to ask you. What part should I adjust to make a date table that has a week interval from the start date to the present date?

Hi Bas! As usual very informative and helpful. But I've gotta question - is it possible to get the max date from fact table in report instead of addin end date manually?

Great ! Thanks

And what if you have more than 1 date column in your report? this threw everything off..

How did you solve: Can't Sort "Day" by "DayNo." due to multiple values for each entry..?

Hi sir, learn a lot from your videos 😊

Hi Bas, this is extremely helpful. Thnx! But ... (there is always a But 😉) ... the schoolorganisation where I work (vmbo/mbo in the Netherlands) we're so occupied teaching that it feels like the ink on one Q-report is still wet or the next Q-report has to be written. So we decided to split the year in thirds (tertial reporting). It gives us time to breath (and teach). How can I add such a tertial number column to my date table? Is there a mod function in Power Query or DAX e.g. (DayNr mod(122))+1?

Man, you are so SMART and HANDSOME! 👏🏼I think I fell in love here. Thanks for this video! It helped me a lot!

Hello, so I'm having this problem (i'm new to power bi). I have let's say types of loan portfolios in one column and then i have dates horizontally and information is filled out under each date for all portfolios. and I'm having problem with this format, because power bi doesn't recognize dates that are horizontally spread, at least not automatically, and I can't seem to build a chart for this time series and these portfolios, when i restructure the data and put all the data vertically including dates then it recognizes the dates. If someone has a solution much appreciated :)