What is a Date Table / Calendar table in Power BI / Excel
Vložit
- čas přidán 23. 08. 2021
- 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 - Jak na to + styl
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.
Wow, very good of you to give such thoughtful feedback. Thank you.
Thanks Wyn, I work for a company with a Financial Year end date of 30 June, so this is so beneficial for me!
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
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 😁
Hah .. you’re welcome Neil
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 CZcams! 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.
Thanks Andrew, I greatly appreciate you talking the time to let me know you found this useful and thanks for the kind comments. Wyn.
The trick that stood out for me was to use the "day of week" for your sort. What a great video!!
Glad it helped Dhani. Thanks for taking the time to leave a kind comment
I respect this guy, what a comprehensive explanation...definitely subscribing
Thanks
An excellent CZcams channel. Thank you very much! Great explanations.
You're welcome, thanks for taking time out to leave a kind comment
Thank you so much this gotta be thebest offerings free o CZcams, no need to fuss anymore with date tables, thaaaaank you 😊
You’re very welcome. Thanks for taking the time to leave a kind comment
Awesome! The most thorough explanation I've seen on the web. Thank you!
Great, glad you found it useful, thanks for leaving a kind comment
Hi Wyn. Awesome tutorial and explanation! Thanks also for access to the Calendar Table file to copy and use.. much appreciated! Thumbs up!!
Cheers Wayne
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.
That’s great to know. Thanks for taking the time to leave a comment
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.
Great glad it helps Hugh, I appreciate you leaving a comment
Great Tutorial, very clear explanation. Thanks Wyn
Thank you so much for including the Calendar Table download file.
You’re welcome. I appreciate you taking the time to let me know you found it useful
This is very helpful, good to hear a Welsh accent as well!
Diolch Nathan
Thank you so much for this! SO incredibly helpful!
Glad it was helpful!
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!!!
You're welcome, thanks for taking the time to leave the kind feedback
Thank you for the explanation. And for the Calendar-Table link.
No worries Iván.
Thank you very much for sharing this knowledge, Wyn
You’re welcome Shadrack, thanks for taking the time to leave a comment
This is very useful to know. Thanks for sharing!
You’re welcome Jeongwon
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 ☺️👍🏻
Thank you very much Sajil. Spread the word ! 😁
As always, great training..
Glad to help, thanks for the comment Sumanth
This is fantastic, thank you so much! :)
You’re welcome 😀
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!! :-)
Cheers Geert, yeah I like that concept of the prefix
classroom speak commonecation software year 2022 2023 🏛🌍🌎🌏🏛🏪🏫🏬🏭🏰💒🏩🏨🏧🏦🏥🏤🏣
classroom microsoft hardware software commonecation speak basic simbol quality in excel year 2022 2023 sys data com master symbol 🏛🌍🌎🌏
Thank is not enough ! Great work
Cheers Vincent
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
Nice one
Very helpful tutorial
Thank you
You’re welcome
fantastic video! Thanks!!!!!!
You’re welcome
Amzing video! Thank you
You’re welcome
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.
Cheers Bernat, that sounds interesting
Amazingly informative.
Glad to help
So great 💥
Thank you!
THANKS AGAIN
You're welcome
Great. I will download.
Thanks for letting me know
Thanks Wyn
No worries Dave
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
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.
Amazing
Thanks
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.
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
@@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. :)
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
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
I’m not quite sure I follow, but do you need to switch the x-axis to categorical rather than continuous?
@@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
@@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
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!
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)
@@AccessAnalytic Thank you so so much!!
@@dbborensztajn No worries
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?
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] )
)
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?
Some items default to 0 for record 1. It’s an odd coding quirk.
You so wonderful
Too kind ☺️
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
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.
Thank you for the calendar table. My month is still spelled in full in the bar chart. Any idea why is that?
Double check you are using the correct column from the Calendar table in your bar chart
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?
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] )
)
)
@@AccessAnalytic thank you for the replay 👍🏻
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.
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 czcams.com/video/5dN4SqufENE/video.html
@@AccessAnalytic Thank you for the tip and response. This helps a lot.
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!
Hi Senthil, are you looking at the Financial Quarter column? Seems OK to me
@@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!
Note the Quarter column won’t change, there’s a separate column called Financial Quarter that does show Q1
@@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.
You’re welcome Senthil
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
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
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"
Yes good call Maksym
Can u help me deal a problem which i am facing to handle multiple data type in a single column in power bi
Maybe add a column using Value.Is then use that to split the column out into multiple columns using a conditional column
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?
found the problem! had to change the column in the table to "Date" rather than "Date/Time"! Great video; thanks!
Glad you solved it!
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.
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
@@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.
I theory: Download the power bi file to your desktop. Double click it and the file with the calendar should open.
@@AccessAnalytic i finally got it to work. many thanks again!
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.
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
Did I hear him say that I need to REBUILD a whole new file ????
Can you let me know what minute of the video you’re referring to
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!
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