DATE TABLE for Power BI using Power Query

Enlarge ur desktop screen

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!

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"

Many Thanks!
this is a beneficial video !!

Thanks, the PQ method was what I was looking for

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 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.

too intimate. dislike.

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

Just use =CALENDARAUTO

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.

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

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

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!

I needed this film so much. Thanks :D

How to combine dataparts and texts? I'd like to create yyyymm column (and similar)

I know I'm late, but you saved my butt. You are the best

FEEDBACK: For beginners your cropped screen view makes it hard to see HOW you got to where you are. Example after setting up the Start and End Date you start to write the function, but I cannot get the Start/End date values out of the fx editor

This was really helpful. Thank you!

I tried to mark as date table but field is grayed out ! Is there a problem

So hard to follow along with this when you dont see the whole screen

Thanks for the amazing knowledge.
Is there any change we can set start date dynamically

This has been so helpful thank you! Would you be able to do a time table tutorial as well?! I'm working with call center data which is proving to be challengng!!

I lost the slicer style "between" when I use the hierarchy using this approach. It now just gives me Vertical or DropDown options for slicer style. Is there a way to make a slicer style "Between" using this table?

thank you

Great video - I really prefer doing this in Power Query and then using it in Power BI rather than using CALENDARAUTO() in DAX

Great Video!!! As always, your videos are full of excellent info!!!! But, how can I create a collumn that calculates the week number, knowing that the week starts always on saturday?? is it possible??? Thank you so much!!!

pls could you show your full screen next time.

Thanks for the video, it is really useful, but not beginner friendly. There might be some crucial steps missing.

thanks for the video, been following along well but i'm getting an error at about 16.5 minutes in when it's time to name it...because it doesn't like in this step that i'm trying to subtract a date from a datetime value: = Duration.Days(Duration.From(EndDate-StartDate))+1. How do i get around this?

Lovely Video. Followed all through. Is this method still okay to apply today or is there a better way boss?

Great Video! However: Sorting the Day by the DayNo doesn't work. It says that in DayNo it is not allowed to have the same value more than once in Day. Weird! Month and Quarter work fine.

Great explanation! But I'm wondering why you made a date hierarchy when you already made the month, quarter, day, etc.. it seems redundant? Why can't you use those ?

Thank you! I have been going nuts trying to figure out how and why graphs would change the order in strange ways when I change the x-axis field. It was super helpful to see how to tie the calendar to other tables and force the desired sort order. Your the first I have found the followed through with this critical step.

Great lesson! Thanks a lot!
But I didn't quite understand the case with the leap year
I have the statistics period 2023-2024 and 2024 - leap year and the end of the year shows 12/30/2024

So this is not working for me, I followed everything perfectly. When I create the visualizations The hierarchy is show all blanks. When I move the data to a table it is looking like the relationship is not really being created even know I have it set. Is this way now obsolete?

Explains it but not the greatest in terms of clicking and clarity

You are awesome!! Thank you for this!

Thank you for such great explanation and showing additional tricks (turning local or global autotime table, etc.). Do you have a video on how to create a dynamic fiscal year table and also that one that doesn't have dates, but just month and year? For example, financial statements and trial balance used to build them doesn't always have dates but rahter month ending balances. "Dynamic" - meaning I could specify the start date to be last 4 full fiscal years + current YTD. So at any given date I'll have my date table that gives me full 4 fiscal years and whatever number of months YTD. and next year it will update the start date to do the same vs having to manually change start date.

I have trouble with DayNo and Day, the data set has 2023 and 2024.. So can't sort Day with DayNo

i want to get startdate and EndDate dynamicaly

More complex

Thank you so much, this was very helpful!

Thanks for the content, I am going to follow through it carefully to recast my dates into FY-Q. I was surprised, however, that you didn't explicitly cover how to change the fiscal year-end date (to March 31, for example). I see you have included a formula for this in the comments so I will try that.

Can you make a video using Dax to create date table ?

i got Confused towards the end of the video 😕😕

Question: What if in my data I have order place date/activation date/Movement date is the Many to Many relationship will work if I connect the dimDate?

Hope someone can answer new to PowerBi. Thanks