What is a Date Table / Calendar table in Power BI / Excel

Рет қаралды 35,170

2 жыл бұрын

What is a Date Table / Calendar table and why do you need one
Link to Free Download of our Power Query Calendar:
accessanalytic.com.au/free-ex...
Link to SQLBI Article on Mark as Date Table
www.sqlbi.com/articles/mark-a...
02:28 Turn off Auto Date Hierarchy
03:52: Copying a Power Query Calendar between Power BI Files
05:20 Changing the Start Date and End Date
07:15 Sorting Months and Days to display correctly
09:47 Changing the Financial Year End, Day of Week and Advanced Editor features
11:28 How the Calendar was built
13:46 Marking Calendar as Date Table
15:46: SQBI link
16:20 Total YTD
18:20 Copying a Calendar table into Excel
Follow me on LinkedIn
/ wynhopkins
Twitter
/ wynhopkins
Access Analytic Training
accessanalytic.com.au/training

Пікірлер: 124

@scottymac734 Жыл бұрын

Yet, another fantastic (and generous) offering. There are lots of Calendar (or Date) suggestions available from many great Power Query gurus (all deserving of the label), but what I particularly like about this one is that start and end dates are easy to define for a given effort, and each step is well documented -- so easy to customize. Very generous to include links to such amazing and well-written downloads. No matter what I learn from other videos I always learn something new here.

@AccessAnalytic Жыл бұрын

Wow, very good of you to give such thoughtful feedback. Thank you.

@neilthackeray181 Жыл бұрын

Who needs Google translate when Wyn translates Geek to English! Thanks for taking the time to show us all that making complicated process is achievable, appreciated 😁

@AccessAnalytic Жыл бұрын

Hah .. you’re welcome Neil

@luda_c 4 ай бұрын

An excellent KZfaq channel. Thank you very much! Great explanations.

@AccessAnalytic 4 ай бұрын

You're welcome, thanks for taking time out to leave a kind comment

@esthercraft 6 ай бұрын

Thank you so much this gotta be thebest offerings free o KZfaq, no need to fuss anymore with date tables, thaaaaank you 😊

@AccessAnalytic 6 ай бұрын

You’re very welcome. Thanks for taking the time to leave a kind comment

@dhanicole Жыл бұрын

The trick that stood out for me was to use the "day of week" for your sort. What a great video!!

@AccessAnalytic Жыл бұрын

Glad it helped Dhani. Thanks for taking the time to leave a kind comment

@ayandapeter1681 Жыл бұрын

I respect this guy, what a comprehensive explanation...definitely subscribing

@AccessAnalytic Жыл бұрын

Thanks

@quentinvandevondel3230 11 күн бұрын

Amzing video! Thank you

@AccessAnalytic 11 күн бұрын

You’re welcome

@nathanwoolls 2 жыл бұрын

This is very helpful, good to hear a Welsh accent as well!

@AccessAnalytic 2 жыл бұрын

Diolch Nathan

@SeelanRobert 10 ай бұрын

Great Tutorial, very clear explanation. Thanks Wyn Thank you so much for including the Calendar Table download file.

@AccessAnalytic 10 ай бұрын

You’re welcome. I appreciate you taking the time to let me know you found it useful

@donaldstafford7657 Жыл бұрын

You are amazing. Thank you so much! It was incomprehensible to be how to understand how to forecast a project’s progress, and this has given me so many steps toward comprehending how all of this works.

@AccessAnalytic Жыл бұрын

That’s great to know. Thanks for taking the time to leave a comment

@wayneedmondson1065 2 жыл бұрын

Hi Wyn. Awesome tutorial and explanation! Thanks also for access to the Calendar Table file to copy and use.. much appreciated! Thumbs up!!

@AccessAnalytic 2 жыл бұрын

Cheers Wayne

@TurboJon 2 жыл бұрын

Awesome! The most thorough explanation I've seen on the web. Thank you!

@AccessAnalytic 2 жыл бұрын

Great, glad you found it useful, thanks for leaving a kind comment

@hughgoble 2 жыл бұрын

Hi, Wyn, thanks very much for this great resource and the explanations. It'll be a great help. I do financial modelling and financial year-end dates are often an issue. your calendar table will totally solve this problem.

@AccessAnalytic 2 жыл бұрын

Great glad it helps Hugh, I appreciate you leaving a comment

@andrewsinha2785 2 жыл бұрын

Thank you once again. This time for the free consultancy answering my question in the most though way. You even did it without me asking for it beautiful ! This is the best site for beginners to intermediate PBI newbies transferring from excel, and there are a lot on KZfaq! Thanks again Andrew. Oh thanks for the link as well. I will use it and I'm sure I will watch this video again and again for all the little best practices and tips on keeping me out of trouble.

@AccessAnalytic 2 жыл бұрын

Thanks Andrew, I greatly appreciate you talking the time to let me know you found this useful and thanks for the kind comments. Wyn.

@andreugeorge Жыл бұрын

Thank you so much for this! SO incredibly helpful!

@AccessAnalytic Жыл бұрын

Glad it was helpful!

@brynorth3586 2 жыл бұрын

I've done a three day course from Microsoft and multiple Linked In Learning classes on Power BI and none come as close to as thorough and well explained as yours. Thank you so much for putting this out there - you're a life saver!!!

@AccessAnalytic 2 жыл бұрын

You're welcome, thanks for taking the time to leave the kind feedback

@IvanCortinas_ES 2 жыл бұрын

Thank you for the explanation. And for the Calendar-Table link.

@AccessAnalytic 2 жыл бұрын

No worries Iván.

@shadrackawunyo7786 2 жыл бұрын

Thank you very much for sharing this knowledge, Wyn

@AccessAnalytic 2 жыл бұрын

You’re welcome Shadrack, thanks for taking the time to leave a comment

@jeongwonc6462 2 жыл бұрын

This is very useful to know. Thanks for sharing!

@AccessAnalytic 2 жыл бұрын

You’re welcome Jeongwon

@Sumanth1601 2 жыл бұрын

As always, great training..

@AccessAnalytic 2 жыл бұрын

Glad to help, thanks for the comment Sumanth

@louism.4980 Жыл бұрын

This is fantastic, thank you so much! :)

@AccessAnalytic Жыл бұрын

You’re welcome 😀

@sopall4424 2 жыл бұрын

Thank is not enough ! Great work

@AccessAnalytic 2 жыл бұрын

Cheers Vincent

@lukev730 10 ай бұрын

Very helpful tutorial Thank you

@AccessAnalytic 10 ай бұрын

You’re welcome

@joseluismoreno4574 Жыл бұрын

fantastic video! Thanks!!!!!!

@AccessAnalytic Жыл бұрын

You’re welcome

@sonyse2t5 2 жыл бұрын

Amazingly informative.

@AccessAnalytic 2 жыл бұрын

Glad to help

@sajilpulath7353 2 жыл бұрын

Thanks Chief , as you mentioned in last video I was waiting for it, then today I created one with my little knowledge and it worked successfully (thank you for the idea to create calendar table). Anyway this table is far better useful. I don’t have power bi at wrk but power pivot. I highly recommend a playlist of your videos in a sequel of beginner to expert☺️ I really wish your tutorials to have more views ☺️ hope adding some hashtags may result better search appearance ☺️👍🏻

@AccessAnalytic 2 жыл бұрын

Thank you very much Sajil. Spread the word ! 😁

@GeertDelmulle 2 жыл бұрын

Hi Wyn, This was not (just) useful,… this was MASSIVELY useful (to put it mildly :-) !! Even though it’s 20’, this is a crash course on the topic of Date/Calendar tables. BTW: I like the prefix tricks of fTable and dTable, so, I call mine dCalendar and that gets rid of those ‘single quotes’ to make it even easier to use. Thanks for sharing this most valuable chunk of knowledge. Great job!! :-)

@AccessAnalytic 2 жыл бұрын

Cheers Geert, yeah I like that concept of the prefix

@rudiansyahsyah9738 2 жыл бұрын

classroom speak commonecation software year 2022 2023 🏛🌍🌎🌏🏛🏪🏫🏬🏭🏰💒🏩🏨🏧🏦🏥🏤🏣

@rudiansyahsyah9738 2 жыл бұрын

classroom microsoft hardware software commonecation speak basic simbol quality in excel year 2022 2023 sys data com master symbol 🏛🌍🌎🌏

@JoshIgoe 2 жыл бұрын

So great 💥

@AccessAnalytic 2 жыл бұрын

Thank you!

@felipesignorellireis7839 2 жыл бұрын

Great. I will download.

@AccessAnalytic 2 жыл бұрын

Thanks for letting me know

@AgulloBernat 2 жыл бұрын

Hi Wyn! You can change the auto date time setting for the current file if you go to the data load section of current file further down on the left hand side of the options menu

@AccessAnalytic 2 жыл бұрын

Nice one

@walidkhlil2872 2 жыл бұрын

THANKS AGAIN

@AccessAnalytic 2 жыл бұрын

You're welcome

@davecope3322 2 жыл бұрын

Thanks Wyn

@AccessAnalytic 2 жыл бұрын

No worries Dave

@AgulloBernat 2 жыл бұрын

I saw a technique in sqlbi (which I can't find now) for month columns which was to use a date ( day 1 of the month of the year 1900) and then use Format string to show the month name (short or long). The cool thing is that it sorts itself. When used in axis you need to set it to categorical.

@AccessAnalytic 2 жыл бұрын

Cheers Bernat, that sounds interesting

@zaheerahmad3896 9 ай бұрын

Amazing

@AccessAnalytic 8 ай бұрын

Thanks

@AndrewGammie Ай бұрын

Absolutely amazing! Thanks so much for this incredible resource I have one question, is there a reason the "Inserted day of week" step applies the days of the week as 0-6 rather than 1-7?

@AccessAnalytic Ай бұрын

Some items default to 0 for record 1. It’s an odd coding quirk.

@Connachtman1916 Жыл бұрын

Thank you, brilliant tutorial, your clear explanation along with excellent diction and pacing make this a wonderful training source. Stunning work all around, much appreciated. i have nabbed your table and will be putting into my dashboards. If I wish to add an ISO week - I have tried but not in any depth as yet - is it relatively easy to add another line to your M code ? Thanks, Connachtman

@AccessAnalytic Жыл бұрын

Too kind, thanks ☺. You can add extra columns easily, I've not done an ISO week but I'm guessing a quick google will give you guidance how.

@abdulrazakabdulkarim7698 2 жыл бұрын

You so wonderful

@AccessAnalytic 2 жыл бұрын

Too kind ☺️

@ghh19727500 Жыл бұрын

thanks for a quick and clear use of calender (and time tables and grouping) as well. I ended up with this very neat date table, however I seem to miss my normal date e.g. as 01.10.2022 under the individual bar regards of the drill down level. Can you do this then we are fast approaching a perfect date table

@AccessAnalytic Жыл бұрын

I’m not quite sure I follow, but do you need to switch the x-axis to categorical rather than continuous?

@ghh19727500 Жыл бұрын

@@AccessAnalytic The X-axis should stay with the year-month-date on the bar chart and I would like a connected line chart showing time to the minut and down 5s, 15s, 30s timeslots. The bar chart shows year-month-day (around 15.09 minut) and would like just the date like 2022-10-03. I hope this is more clear

@AccessAnalytic Жыл бұрын

@@ghh19727500 If you just add date it should work (you may have to format it as yyyy-MM-dd by clicking on the date in the Right-hand Fields panel and go to the Formatting Section of the Column Tools ribbon and type in yyyy-MM-dd

@dbborensztajn Жыл бұрын

Amazing video, thanks so much for putting in the time. How would i go about changing format of Finaincial Year so it shows "FYxx" (e.g. "19-20" as "FY20")? Thank you in advance!

@AccessAnalytic Жыл бұрын

Hi, you can edit a step in my Calendar file (see description for link) - The step is called "Added Financial Year Range" to be ="FY"& Text.End([Financial Year End],2)

@dbborensztajn Жыл бұрын

@@AccessAnalytic Thank you so so much!!

@AccessAnalytic Жыл бұрын

@@dbborensztajn No worries

@wmfexcel 2 жыл бұрын

Thanks for the video! A question. Per my understanding, the calendar table has to be cover a full year in order to have the time intelligence function worked. My question is, when we have to created a custom calendar table that runs on Fiscal Year (e.g. a 4-5-4 calendar that starts in Feb), those YTD/MTD functions won't work and we have to write some DAX code to deal with it. In this case, is it still required to have a calendar table that covers the entire fiscal year? Appreciate your advice.

@AccessAnalytic 2 жыл бұрын

I’d suggest it’s generally best practice for your calendar to start at the beginning of your Financial year. Once you start writing custom DAX for your filtering it’s not as important as when you are using built in TOTALYTD or DATESYTD

@wmfexcel 2 жыл бұрын

@@AccessAnalytic Thanks for your prompt response. Yes. Totally agree with you on the best practice. I am just wondering if that's necessary. Glad to have an expert's opinion. :)

@AccessAnalytic 2 жыл бұрын

I’d say no it’s not necessary , but I still don’t understand why TOTALYTD wouldn’t work without all dates in the year being present in the calendar , so I’m clearly missing some nuanced point there

@gracie_c 3 ай бұрын

Thank you for the calendar table. My month is still spelled in full in the bar chart. Any idea why is that?

@AccessAnalytic 3 ай бұрын

Double check you are using the correct column from the Calendar table in your bar chart

@colinracheljustpootling7468 Жыл бұрын

Just learning powerbi and this has been really useful. I have a question, as when I try to create a new quick measure i cannot use the calendar date and I get a message saying only hierarchy dates can be used. Is this correct and does it mean only a measure created with DAX is available? Thanks

@AccessAnalytic Жыл бұрын

If you right-click on the Calendar table and choose Mark as date table, that may help. The general recommendation is to avoid the use of quick measure generated DAX and write the DAX yourself. The quick measures are limited and can hamper learning.

@iamspeedster Жыл бұрын

You don't need to reimport data as Auto Date/Time can be turned off also in the settings. Theres is a Section called "Current File"

@AccessAnalytic Жыл бұрын

Yes good call Maksym

@deepakjha-lj4un Жыл бұрын

Can u help me deal a problem which i am facing to handle multiple data type in a single column in power bi

@AccessAnalytic Жыл бұрын

Maybe add a column using Value.Is then use that to split the column out into multiple columns using a conditional column

@vghc Жыл бұрын

Is there a way to do like a "sysdate-3" in PowerBi? I just a drop down, so users can pick 1,3,7,15,30,45 days.

@AccessAnalytic Жыл бұрын

Yep potentially. I don't have a video on that exact thing but the concept would relate to my "Days Since Today" / Months since today" columns that I call out in this video kzfaq.info/get/bejne/a8p-Z7ai2sypfnk.html

@vghc Жыл бұрын

@@AccessAnalytic Thank you for the tip and response. This helps a lot.

@senthilramana 2 жыл бұрын

Thanks for this video. I have changed my financial year last month to 3 (My financial year starts by Apr) but the qtr number remains the same. For example, Jan should show as Qtr4 but it shows Qtr1. Kindly help to solve this. Thanks!

@AccessAnalytic 2 жыл бұрын

Hi Senthil, are you looking at the Financial Quarter column? Seems OK to me

@senthilramana 2 жыл бұрын

@@AccessAnalytic Kindly change the Financial year last month to 3 and check whether April shows as Qtr 1. Or after changing the last month to 3, should I go and change the Qtr number too somewhere? Thank you!

@AccessAnalytic 2 жыл бұрын

Note the Quarter column won’t change, there’s a separate column called Financial Quarter that does show Q1

@senthilramana 2 жыл бұрын

@@AccessAnalytic got it. I have missed that out. Thanks for the clarification. Your way of explaining things are too good in the video. Thanks for sharing your knowledge with us.

@AccessAnalytic 2 жыл бұрын

You’re welcome Senthil

@sajilpulath7353 2 жыл бұрын

Hi chief, question! I have an important data and sales data, I was trying to create a relationship by batch number, but due to duplicate values in the batch number column, I can’t create the relationship. How to overcome this?

@AccessAnalytic 2 жыл бұрын

You have to create a common "bridging" table that you link the 2 fact tables to. This can be done using Power Query to reference each fact table and just keep the batch number column from each and then append the 2 referenced queries to each other and remove duplicates. Or create a table in DAX using DAX Batch Bridging Table = DISTINCT( UNION( ALL( Fact1[Batch Number] ), ALL( Fact2[Batch Number] ) ) )

@sajilpulath7353 2 жыл бұрын

@@AccessAnalytic thank you for the replay 👍🏻

@murjika 2 жыл бұрын

Is it possible to give us DAX code for the calendar table, because when I try to download the file I get a security warning and while I trust your channel I am not allowed to accept enable contents warning given our corporate security policy. Thank you

@AccessAnalytic 2 жыл бұрын

Try this Mamuka, you'll need to paste the code into the Advanced Editor ( the language is M rather than DAX ) gist.github.com/wynhopkins/7c97d2482433780d4a47f52b2f444c01

@user-sw8ml2wj5e Ай бұрын

Hi, I added the date table after already beginning my dashboard. So the date hierarchy already exists in my data table. I can't seem to find a way to delete it at the data level. I created the calendar table, and followed your steps, but the table shows the year/month as (blank). Does the error have something to do with the hierarchy already in existence in the data table? Is there are way to fix this?

@user-sw8ml2wj5e Ай бұрын

found the problem! had to change the column in the table to "Date" rather than "Date/Time"! Great video; thanks!

@AccessAnalytic Ай бұрын

Glad you solved it!

@matthewbelitz2950 12 күн бұрын

I must be dense, I didn't last 4 minutes before I was lost...you opened up a report with a pre-built calendar, where did you get the pre-built calendar from? Without that I couldn't continue on. Guess it's just me.

@AccessAnalytic 11 күн бұрын

Hi, at 3:30 a message pops up saying check the description for a link to the Calendar Table. Here’s the link accessanalytic.com.au/free-excel-stuff/free-excel-templates/ At 11:28 I show how I built it

@TurboJon 2 жыл бұрын

I tried to download and use the date table but couldn't do it. Keeps asking for an updated version of PBI which I have. Can you help with step x step instructions? thanks.

@AccessAnalytic 2 жыл бұрын

Are you using the Excel one or the Excel one? I've just downloaded the Power BI one and double clicked the file and it opened fine

@TurboJon 2 жыл бұрын

@@AccessAnalytic the powerbi one. The excel one does seem to have the table just instructions. Obviously I’m doing something wrong or download the wrong thing. Thanks for your help on this. I can’t wait to try it.

@AccessAnalytic 2 жыл бұрын

I theory: Download the power bi file to your desktop. Double click it and the file with the calendar should open.

@TurboJon 2 жыл бұрын

@@AccessAnalytic i finally got it to work. many thanks again!

@markk364 3 ай бұрын

Did I hear him say that I need to REBUILD a whole new file ????

@AccessAnalytic 3 ай бұрын

Can you let me know what minute of the video you’re referring to

@jerrydellasala7643 2 жыл бұрын

These would be great if the files were ACTUALLY available. And don't say "there's a link...". It $ucks! I've downloaded every file there (including the "Add In" that screwed up Excel until I got rid of it!), and none of the data you use here except the blank calendar are there, making this a waste of time!

@AccessAnalytic 2 жыл бұрын

So if you click on the Power Query Calendar link here accessanalytic.com.au/free-excel-stuff/free-excel-templates/ then you have access to the Calendar Tables and Time Table. This is what this video is about and what I'm sharing. If you haven't got your own data then scroll down the same "free stuff" web page and there's a section on dummy data sets. I've tried to make things as obvious as possible, other comments here tend to indicate others found it OK