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

[@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

[@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

[@luda_c]

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

[@AccessAnalytic]

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

[@ayandapeter1681]

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

[@AccessAnalytic]

Thanks

[@Looii5]

Thanks Wyn, I work for a company with a Financial Year end date of 30 June, so this is so beneficial for me!

[@AccessAnalytic]

Excellent, yes many of my clients have 30 June year end. I appreciate you taking the time to let me know you found it useful

[@Adrian-p4j]

You're great. Why aren't there teachers like that at school? Great stuff 😊👍

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@esthercraft]

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

[@AccessAnalytic]

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

[@brynorth3586]

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]

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

[@nathanwoolls]

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

[@AccessAnalytic]

Diolch Nathan

[@SeelanRobert]

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

[@AccessAnalytic]

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

[@andrewsinha2785]

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 KZbin! 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]

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

[@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

[@TurboJon]

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

[@AccessAnalytic]

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

[@wayneedmondson1065]

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

[@AccessAnalytic]

Cheers Wayne

[@IvanCortinas_ES]

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

[@AccessAnalytic]

No worries Iván.

[@hughgoble]

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]

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

[@GeertDelmulle]

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]

Cheers Geert, yeah I like that concept of the prefix

[@rudiansyahsyah9738]

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

[@rudiansyahsyah9738]

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

[@andreugeorge]

Thank you so much for this! SO incredibly helpful!

[@AccessAnalytic]

Glad it was helpful!

[@lukev730]

Very helpful tutorial Thank you

[@AccessAnalytic]

You’re welcome

[@quentinvandevondel3230]

Amzing video! Thank you

[@AccessAnalytic]

You’re welcome

[@sajilpulath7353]

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]

Thank you very much Sajil. Spread the word ! 😁

[@jeongwonc6462]

This is very useful to know. Thanks for sharing!

[@AccessAnalytic]

You’re welcome Jeongwon

[@shadrackawunyo7786]

Thank you very much for sharing this knowledge, Wyn

[@AccessAnalytic]

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

[@AgulloBernat]

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]

Nice one

[@Sumanth1601]

As always, great training..

[@AccessAnalytic]

Glad to help, thanks for the comment Sumanth

[@louism.4980]

This is fantastic, thank you so much! :)

[@AccessAnalytic]

You’re welcome 😀

[@jardelH]

Muuuuuuuuuito obrigado! Excelente!

[@AccessAnalytic]

You're welcome

[@sopall4424]

Thank is not enough ! Great work

[@AccessAnalytic]

Cheers Vincent

[@mikomori9623]

Very helpful cheers mate

[@AccessAnalytic]

No worries

[@sonyse2t5]

Amazingly informative.

[@AccessAnalytic]

Glad to help

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

[@AgulloBernat]

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]

Cheers Bernat, that sounds interesting

[@joseluismoreno4574]

fantastic video! Thanks!!!!!!

[@AccessAnalytic]

You’re welcome

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

[@JoshIgoe]

So great 💥

[@AccessAnalytic]

Thank you!

[@ekt9448]

Thanks for the great video! In the examples, you had the calendar table linked to the 3 date fields, but everytime you pull out a field from the calendar table, it’s based on OrderDate. Do I need another calendar table if i want it based on DueDate instead?

[@AccessAnalytic]

No, you can create another inactive ( dotted ) relationship to Due Date and then write a measure to use that relationship Sales Due = CALCULATE( [Sales], USERELATIONSHIP( Calendar[Date], FactTable[Due Date] ) )

[@gracie_c]

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

[@AccessAnalytic]

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

[@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

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

[@felipesignorellireis7839]

Great. I will download.

[@AccessAnalytic]

Thanks for letting me know

[@walidkhlil2872]

THANKS AGAIN

[@AccessAnalytic]

You're welcome

[@wmfexcel]

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]

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]

@@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]

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

[@davecope3322]

Thanks Wyn

[@AccessAnalytic]

No worries Dave

[@zaheerahmad3896]

Amazing

[@AccessAnalytic]

Thanks

[@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

[@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

[@zoeh2381]

Nice video - I have a predicament I haven't been able to find an answer to and am wondering if you may have any advice. I am trying to use power BI to graph/track a series of forecasted completion dates overtime. The data input I have is a excel file that is saved weekly that reports forecasted dates for a series of projects. I have figured out how to combine and transform the data within a query but am struggling with a way to setup a visual to display this. PBI seems to be unable to use dates as a value, it will only "count" despite all adjustments to data type and summarization settings. I am trying to see (ideally) an x-axis with file name (displayed/sorted as workweek) and a y-axis with dates - so that users can visualize the trend of how a forecasted date has moved over time. I've experimented a little with data tables and will try downloading and plugging in your query - but am skeptical this is the fix that will work for my issues. If anyone has any advice, I welcome it!!!!!

[@AccessAnalytic]

I don’t know sorry. Try posting to www.reddit.com/r/PowerBI/ or community.powerbi.com/t5/Desktop/bd-p/power-bi-designer

[@Tobias-h9j]

what shall i do when i have a "fiscal" day - so my actual day goes from 01.01.2024 06:00 o'clock until 02.01.2024 05:59 but it should always show 01.01.2024

[@AccessAnalytic]

I’d add an adjusted date column to your fact table. Use a custom column in Power Query. if [OriginalTime] < Time.FromText("6:00am") then Date.AddDays([Date],-1) else [Date]

[@Tobias-h9j]

@@AccessAnalytic thanks but this would mean i have to create them for every table i am using, and this is not working for direct queries to databases - i created a calendar from 2020 until next year with 1 hour increment - and calculated the fiscal day onto that increment - all good - but as i promoted that to power bi service i got only the first year and some days. is there a limit in power bi?

[@AccessAnalytic]

There shouldn’t be. If it did work I think you’re going to got more and more calculation and performance issues if you continue with that approach. What’s the data source? why direct query? can you change to import? can the data source owner add the adjusted field?

[@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 kzbin.info/www/bejne/a5WxZYanqst4hKc

[@vghc]

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

[@abdulrazakabdulkarim7698]

You so wonderful

[@AccessAnalytic]

Too kind ☺️

[@CharlesRo-o6k]

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?

[@CharlesRo-o6k]

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!

[@sajilpulath7353]

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]

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]

@@AccessAnalytic thank you for the replay 👍🏻

[@senthilramana]

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]

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

[@senthilramana]

@@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]

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

[@senthilramana]

@@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]

You’re welcome Senthil

[@murjika]

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]

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

[@TurboJon]

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]

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]

@@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]

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

[@TurboJon]

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

[@matthewbelitz2950]

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]

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

[@markk364]

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

[@AccessAnalytic]

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

[@jerrydellasala7643]

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]

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