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

Подписаться 89 тыс.

Просмотров 35 тыс.

50% 1

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

Хобби

Опубликовано:

23 авг 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 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 RU-vid channel. Thank you very much! Great explanations.

@AccessAnalytic 4 месяца назад

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

@dhanicole 2 года назад

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

@AccessAnalytic 2 года назад

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

@esthercraft 6 месяцев назад

Thank you so much this gotta be thebest offerings free o RU-vid, 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

@quentinvandevondel3230 16 дней назад

Amzing video! Thank you

@AccessAnalytic 16 дней назад

You’re welcome

@nathanwoolls 2 года назад

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

@AccessAnalytic 2 года назад

Diolch Nathan

@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 RU-vid! 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.

@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

@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

@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

@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

@andreugeorge Год назад

Thank you so much for this! SO incredibly helpful!

@AccessAnalytic Год назад

Glad it was helpful!

@IvanCortinas_ES 2 года назад

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

@AccessAnalytic 2 года назад

No worries Iván.

@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

@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

@lukev730 10 месяцев назад

Very helpful tutorial Thank you

@AccessAnalytic 10 месяцев назад

You’re welcome

@sopall4424 2 года назад

Thank is not enough ! Great work

@AccessAnalytic 2 года назад

Cheers Vincent

@louism.4980 Год назад

This is fantastic, thank you so much! :)

@AccessAnalytic Год назад

You’re welcome 😀

@joseluismoreno4574 Год назад

fantastic video! Thanks!!!!!!

@AccessAnalytic Год назад

You’re welcome

@sonyse2t5 2 года назад

Amazingly informative.

@AccessAnalytic 2 года назад

Glad to help

@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 🏛🌍🌎🌏

@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 ! 😁

@felipesignorellireis7839 2 года назад

Great. I will download.

@AccessAnalytic 2 года назад

Thanks for letting me know

@JoshIgoe 2 года назад

So great 💥

@AccessAnalytic 2 года назад

Thank you!

@walidkhlil2872 2 года назад

THANKS AGAIN

@AccessAnalytic 2 года назад

You're welcome

@davecope3322 2 года назад

Thanks Wyn

@AccessAnalytic 2 года назад

No worries Dave

@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

@zaheerahmad3896 9 месяцев назад

Amazing

@AccessAnalytic 9 месяцев назад

Thanks

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

@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

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

@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

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

@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

@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

@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 👍🏻

@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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dN4SqufENE.html

@vghc Год назад

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

@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

@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

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

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

@matthewbelitz2950 16 дней назад

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 15 дней назад

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