Sign in

A question that, when googled, will display pages of blogs and opinions. According to Microsoft there are at least 5 different options for creating a Power BI calendar, with pros and cons for each. Which one you use depends on what the table is for, your programming experience, what data you have access to and your own personal preference (or the preference of the person you’re asking for advice!).

The flow diagram below aims to help you decide which solution is best for your particular need. …


If your Power BI project has more than one table with a date column or if you want to report on time periods other than year, month and quarter then you should create a calendar table. You may also see this referred to as a calendar or date dimension.

What is a calendar table?

A calendar table is a table of data with at least one column containing dates. This date column has to span across full years, it cannot contain blanks or have missing dates and all dates must be unique. For example, if your calendar table spans across…


If you don’t have access to a date table in a source database or if you’re new to Power BI and aren’t that comfortable with programming in DAX or M then sign up to my mailing list and get the link to download a ready-made excel date table. You can import this into Power BI, mark as a date table and start using it straight away.

What does the excel date table contain?

The default file contains a date table for the years 2000–2023 with 22 additional columns of information (the date range can be changed within the excel file)…


So you’ve decided to create a date table in Power BI using DAX, good choice! If you’re new to DAX this blog will guide you through creating the table from start to finish. If you already know how to use DAX and just want to copy some calendar code go directly to the code section here.

If you’re still unsure why you need a calendar table check out this blog first: What is a calendar table and why should I create one in my Power BI project?

TURN OFF AUTOMATIC DETECTION OF DATES

Before you start writing any DAX you…


Using Power Query M to create a date table in Power BI is recommended by many Power BI developers, mainly because Power Query is where all data prep (transformations) take place.

This blog walks you through the stages of creating a Power Query date table, using the in-built functionality to create columns of date information. The great thing about using Power Query is that you can save the table’s M code and paste it into new Power BI projects to quickly set up a date table. …


You can quickly set up a date table in Power Query by simply copying the M code below into a blank query. The steps used to generate the code are explained in this blog here.

Follow these steps to set up an M code date table in just a few minutes:

  1. First, create a blank table:
    Click the Get Data button and select Blank Query from the drop down list.
    This opens up a blank query in the Power Query window
  2. Next, rename the empty query that has been created:
    Change the name in the query setting panel on the right…

Change from sorting months Apr, Aug, Dec to Jan, Feb, Mar:

This method creates a separate month table and brings it into Power BI to order months chronologically.

I have a video on my YouTube channel explaining this method or you can use this easy-to-follow guide:

STEP 1
Create and save a month table like the one below, it can be in any format,
Here’s a ready made file in excel you can use to save you time typing out the month names!


Living in a city is like living inside a kettle.

You don’t realise it but slowly over the years limescale accumulates on you. The longer you stay in the city the thicker that layer of limescale becomes, slowly desensitising you to anything external to you.

If you were born in the city and lived in one your whole life, you will think this is normal. You think having thick layers of limescale is normal and part of being an adult. Empathy is something to learn rather than something that should come naturally. …


Handy extras to take on your travels

Here are a few items that I’ve found useful during my 18 months travelling through Indonesia and Malaysia. They aren’t essential packing but they do make travelling that little bit easier.

I have an even longer list of items I bought and didn’t use…maybe an idea for the next post!

Speaker

Can’t beat a good quality travel bluetooth speaker, for chilled out evenings listening to podcasts or impromptu beach parties

Hooks

B&B’s, hotels and homestays rarely have enough hooks.
Suction hooks are good for hanging towels, bags, bananas etc. S hooks are also useful where suction hooks won’t stick.

Water Bottle

A small water…

BI-Jo

I’m a data consultant with over 20 years experience. I want to help demystify data and it’s uses, essentially help people to ‘talk data’.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store