Useful websites to help you create a Power BI theme

Researching how to create Power BI themes can take days. The official Microsoft documentation is comprehensive but confusing and the gallery has themes that change the colour scheme but not the report formatting.   

This blog lists the best resources to help you create your own theme from scratch. Alternatively, if you don’t have time or the desire to create one yourself you can visit my theme store.

BI-Jo YouTube Tutorial


A 20-minute tutorial that guides you through creating a theme from beginning to end. It starts by setting the colour scheme using the customize theme dialogue box in Power BI and then shows you how to refine the theme in the JSON file. 

Github

This Github page has the formatted JSON code for each visual type, just copy and paste directly into your theme file and change the parameters to suit your theme’s style. Great for those who struggled while trying to make sense of the Power BI report themes documentation

Test Power BI report

The Kratos BI Visuals Power BI report has all the standard visuals on a single page. You download the pbix file, load your theme and check how it impacts all visuals. 

Coolors.co

A website to create the perfect palette for your Power BI report or to get inspired by thousands of beautiful colour schemes. This is the best website I’ve found for selecting a theme’s colours, you can save palettes, generate gradients, check for colour blindness suitability and even download the palettes in code format. 

Theme JSON file generator

A JSON file generator. Pick the colours and parameters for each Power BI visual and download the JSON file it generates. It’s a great resource to start creating a theme, but it doesn’t include all parameters for all visuals.

JSONLit

Validates and formats JSON code, great when downloading JSON theme files that load the code into a single line.

BI-Jo theme store

A selection of Power BI themes. Each theme has a modern colour scheme and will reformat your report in just a few clicks.

Simply load the theme to your pbix file and all formatting is taken care of, without the need for any additional configurations. Can save you hours (or days if you’re a perfectionist like me!) of tinkering with the look and feel of each visual.

What method should you use to create a date table in Power BI?

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. It is not meant to be a definitive answer but aims to help you in your decision process, especially if you’re new to Power BI.

I’ve create a step by step guide for each of the DAX, M and Excel date table solutions, a link to each blog can be found 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?


Processing…
Success! You're on the list.

Cover photo by Brooke Lark on Unsplash

What is a date table and why should I create one in my Power BI project?

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 date 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 3 years it should have 1,095 rows, one row for each day. Typically a calendar table has additional columns such as year, month, quarter, day of week, weekend flag but it doesn’t have to.

Why do I need a date table?

There are three main reasons why you should create a calendar table in Power BI:

  1. A calendar table helps you report on any time period you need to. For example: to report on retail sales figures by day of week, view accounts by financial years, comparing sales this week to those the same week last year, to filter and slice data by weekdays and weekends etc. 
  2. Power BI has some fancy time intelligence functions to help you manipulate data by different time periods and you need a calendar table defined to use these.
  3. A calendar table can be used as a link between different data sets in your report. For example, if you want to see if there is a correlation between the weather and number of litter tickets issued in a city, you would link these datasets via the calendar table.
Explains how to connect the date table to other tables in Power BI

How do I create a date table?

There are many different ways to create a calendar table in Power BI, I’ve created this easy-to-follow flow diagram to work out which is the best method for you.

Processing…
Success! You're on the list.

Cover photo by Estée Janssens on Unsplash

How to create a date table in Power BI with a ready-made Excel file

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). Here’s a snapshot of the first week’s data:

The excel file has an instructions tab that explains how to change the date range, the financial year starting month, the week day starting day, which days are classed as weekends and how to add public holidays.

How to set up the date table in Power BI

Follow these steps to import the excel table into Power BI and set it up as a date table:

  1. Firstly, turn off the auto date/time detection in Power BI
    1. File menu – Options and settings – Options
    2. Select Data Load under the CURRENT FILE heading
    3. Untick Auto date/time
  2. Load the ‘Date table’ tab in the excel file into Power BI using the Get Data button
  3. Set the table as an official Date Table
    1. Click on the table name in the fields panel (right hand side of screen)
    2. Click on the 3 dots that appear
    3. Select Mark as Date Table
    4. Select the date column from the drop down list
  4. Delete any of the calendar columns that you don’t require in your report. This is recommended for performance reasons.
  5. And now your table is ready to be used!

List of table columns with descriptions of content.

DateDate in UK format (can be changed to US format in Power BI)
DayDay of year number starting from Jan 1st
Day of weekDay of week name – Monday, Tuesday etc
Day of week shortShort day of week name – Mon, Tue, Wed etc
Day of week letterFirst letter of the day of week name – M, T, W etc
Day of week numberWhere Monday = 1 and Sunday = 7
WeekendYes for weekends – No for weekdays
Week NumberWeek number where 1 = first week of January and 52 = last week in December
MonthMonth name
Month NumberMonth number where where January = 1 and December = 12
Month YearShort month name and year i.e Jan 2020
Year Month NumberYear and month number i.e 202001
Month ShortShort month name – Jan, Feb, Mar etc
Month LetterFirst letter of the month name
QuarterQuarter based on calendar year – Q1 = Jan, Feb, Mar
YearYear
Year ShortLast 2 numbers of the year
Financial Month NumberMonth Number where April = 1 and March = 12
Financial QuarterQuarter based on financial year – Q1 = Apr, May, Jun
Financial YearFinancial Year
Financial Year DisplayYears spanning the financial year i.e 2019-2020
Public HolidayYes/No flag to show whether the day is a public holiday – only Christmas day is included in the sample but you can add all public holidays to the look up tab in the excel file.
Public Holiday DescriptionDescription of the public holiday if the date is a public holiday i.e Christmas Day (will have n/a if it’s not a public holiday)

Processing…
Success! You're on the list.

How to create a DAX date table

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’d rather watch a video explaining the steps involved then check out the accompanying YouTube video 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 need to turn off the Power BI auto date/time option. This is switched on by default when you set up a Power BI project and creates a hidden date/time table for each date column. This is great if you need basic calendar columns but should be turned off if you’re creating your own data table.

  1. Turn the Auto date/time off in the Options dialog box (File menuoptions and settings options).
  2. Click on Data Load under the current file heading and untick Auto date/Time

CREATING THE DATE COLUMN

The minimum requirement for a Power BI date table is a table 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. There are 2 ways to create this column using DAX:

– CALENDAR requires you to input a start and end date
– CALENDARAUTO searches all tables in Power BI to identify the minimum and maximum date, it then returns a column of dates starting from 1st Jan of the minimum date year and 31 Dec of the maximum date year.

This example uses the CALENDAR option:

  1. First you need to create a blank table in which to write the DAX.
    Go to the Modelling Menu – select New Table
  2. Next you use DAX to create the date column in the code box that appears
    Replace Table = with the following:

    Date Table = CALENDAR (DATE (2000, 1, 1), DATE (2023, 12, 31))

    Date Table is the table name and the code creates a date column spanning the years 2000 to 2023 (change to the years you require)

SET THE TABLE AS A POWER BI DATE TABLE

  1. Click on the table name in the fields panel (right hand side of screen)
  2. Click on the 3 dots that appear
  3. Select Mark as Date Table
  4. Select the date column from the drop down list

ADDITIONAL DATE INFO COLUMNS

Your date table is now ready to be used! However it will be far more useful if you add some additional date columns such as month name, year, quarter etc.

First add a new column to the date table:

  1. Click on the date table name in the fields panel (right hand side of screen)
  2. Click on the 3 dots that appear
  3. Select New Column

This creates a new column and opens a formula bar ready for you to name the column and add the DAX code. To start with we’ll add a column containing the day of the month. In the code below Day is the column name and [Date] is the date column previously created with the CALENDAR function:

Day column:

Day = Format([Date],”d”)

Next add a month column:

  1. Create a new column
  2. In the formula bar add the DAX
    Month = Format([Date],”MMMM”)

Now add a year column:

  1. Create a new column
  2. In the formula bar add the DAX
    Year = FORMAT([Date],”yyyy”)

DAX CODE FOR NEW DATA INFORMATION COLUMNS

Now you have a far more useful date table with a day, month and year column. There are many more date information columns you can add, which ones you use depends on your preference and report requirements.

Below is a list of DAX code that can be used to create 19 date information columns, you can pick and choose which columns you want to add to your table. For performance purposes you should only create columns that you will be using in your report.

Quarter – the quarter of the calendar year (Q1, Q2, Q3, Q4)

Quarter = "Q"&Format([Date], "q")

Month Number – calendar Month Number, Jan = 1, Feb = 2 etc

Month Number= Format([Date],"M")

Month Short – short month name i.e Jan, Feb, Mar

Month Short= Format([Date],"MMM")

Month Letter – first letter of the month name

Month Letter= LEFT(Format([Date],"MMMM"),1)

Year Short – last 2 numbers of the year e.g 10 for the year 2010

Year Short= FORMAT([Date],"yy")

Month Year – short month name and year e.g Jan 2000

Month Year= Format([Date],"MMM") & " " & FORMAT([Date],"yyyy")

Year Month Number – year and month number (used to order months over years) i.e 200012 for Dec 2000

Year Month Number = FORMAT([Date],"yyyy") & Format([Date],"MM")

Financial Month Number – creates the month number based on financial year.
Here the example sets the financial year to start in April, change the variable
FinancialStartMonth to the month number that your financial year starts at.

Financial Month Number =
VAR FinancialStartMonth = 4
RETURN
if(Month([Date]) >= FinancialStartMonth, 
Month([Date]) - FinancialStartMonth + 1, 
Month([Date]) + (12 - FinancialStartMonth) +1)

Financial Quarter – creates the quarter based on the financial year.
Here the example sets the financial year to start in April, change the variable
FinancialYearStartMonth to the month number that your financial year starts at.

Financial Quarter=
Var FinancialYearStartMonth = 4
var Mn = MONTH([Date])
RETURN
if(Mn < FinancialYearStartMonth,
"Q"&FORMAT(ROUNDUP((Mn - FinancialYearStartMonth + 13)/3,0),""),
"Q"&FORMAT(ROUNDUP((Mn - FinancialYearStartMonth + 1)/3,0),""))

Financial Year – creates the financial year, Here the example sets the financial year to start in April, change the variable FinancialYearStartMonth to the month number that your financial year starts at.

Financial Year =
VAR FinancialYearStartMonth = 4
RETURN
IF(MONTH([Date])>= FinancialYearStartMonth,YEAR([Date]), YEAR([Date])-1)

Financial Year Display – creates a user friendly financial year display e.g 2000-2001.
Here the example sets the financial year to start in April, change the variable
FinancialYearStartMonth to the month number that your financial year starts at.

Financial Year Display =
VAR FinancialYearStartMonth = 4
RETURN
IF(MONTH([Date])>=FinancialYearStartMonth, 
YEAR([Date])&"-"&YEAR([Date])+1,
YEAR([Date])-1&"-"&YEAR([Date]))

Day of Week – day of week name e.g Monday

Day of Week = Format([Date],"dddd")

Day of Week Short – short day of week e.g. Mon, Tue, Wed

Day of Week Short = Format([Date],"ddd")

Day of Week Letter – Shortened day of week e.g M for Monday Th for Thursday

Day of Week Letter = SWITCH(Format([Date],"dddd"), 
"Monday","M", "Tuesday","T", "Wednesday","W", 
"Thursday","Th", "Friday","F", "Saturday","Sa", "Sunday","S")

Day of Week Number – Number of the day of week where Monday = 1. Change the 2 in the code to 1 to start the week on a Sunday.

Day of Week Number =WEEKDAY([Date],2)

Weekend – Sets the field to a Yes if the day is a Saturday or Sunday and No for all other days. If your weekends are Friday and Saturday change the code accordingly.

Weekend = SWITCH(Format([Date],"dddd"), 
"Sunday", "Yes", "Saturday", "Yes","No")

Week Number – The week of year number, this example is where the week starts on a Monday. If you want the week to start on a Sunday change the 2 to 1.

Week Number= WEEKNUM([Date],2)

Processing…
Success! You're on the list.

Cover photo by Adam Tinworth on Unsplash

How to create a Power Query M date table

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. If you’d prefer to copy and paste the final M code and set up a table quickly now, then jump to the code section here.

If you’d rather watch a video explaining the steps involved then check out the accompanying YouTube video 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?

STEP 1: TURN OFF AUTOMATIC DETECTION OF DATES

Before you start writing any code you need to turn off the Power BI auto date/time option. This is switched on by default when you set up a Power BI project and creates a hidden date/time table for each date column. This is great if you need basic calendar columns but should be turned off if you’re creating your own data table.

  1. Turn the Auto date/time off in the Options dialog box
    File menuOptions and settings Options

  2. Under the Current File heading click on Data Load and under the Time intelligence section untick Auto date/timeOK

STEP 2: CREATE THE DATE COLUMN

The minimum requirement for a Power BI date table is a table 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.

The following steps walks you through setting this date column up using M query:

  1. First create a blank table:
    Click the Get Data button from the Home menu 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 of the screen, to something like Date Table or Calendar

  3. Now create the date column:
    a. View menu Advanced Editor
    b. Replace the text in the editor with the code below. Change the Start and End variables to the start and end of the years you wish your date table to cover. The code below is set to run from 1st Jan 2000 to 31st Dec 2023 – click Done
let
 Start= #date(2000,1,1),  // add a start date (yyyy,m,d)
 End = #date(2023,12,31), // add an end date (yyyy,m,d)
 DateList = List.Dates(
 Start, Number.From(End)- Number.From(Start)+1,
 #duration(1,0,0,0))
 in
     DateList
  1. Next convert the list of dates to a table
    Transform menu under List Tools then click the To Table button – OK

  2. Rename the column to Date (right click on column name – rename)

  3. Set the column format to Date
    Click on the symbol to the left of the column name and select Date from the drop down list

STEP 3: CREATE BASIC DATE INFORMATION COLUMNS

You now have a usable date table with a single column of dates. However, it will be far more useful if you add additional columns such as Month Name, Year, Quarter etc. These can be quickly added from the Power Query menus:

  1. First let’s add the Day of month:
    Add Column menu – Date Button drop down – select Day then Day again

  2. Next add the Month name
    Add Column menu – Date Button drop down – select Month then Name of Month
    If the Date button is grayed out first click on the Date column header

  3. Next add the Year
    Add Column menu – Date Button drop down – select Year then Year again

  4. Finally add the Quarter:
    Add Column menu – Date Button drop down – select Quarter then Quarter of Year.
    If you prefer the quarter to display like this: Q1, Q2, Q3 Q4 you can use the code below:
    a. Click on the Quarter column heading
    b. Replace the code in the formula bar above the table with the code below
    c. Change the column format to text
= Table.AddColumn(#"Inserted Year", "Quarter", 
each "Q"&Number.ToText(Date.QuarterOfYear([Date])), 
Int64.Type)

STEP 3 – ADD ADDITIONAL COLUMNS USING M CODE

You’ve now added basic date columns using the date menu in Power Query. You can continue to add columns from the Date drop down list but if you want to add non-standard columns such as Financial Month, Financial Quarter and Weekend flags then you’ll need to start some coding!

Don’t worry you can use the Power Query interface to simplify the task. The following steps walk you through creating a Financial Month column. The steps can be repeated to add other non-standard date columns.

  1. Open the custom column dialog box
    Add Column menu – click on the Custom Column button

  2. Change the column name to Financial Month Number

  3. Paste the code below into the main body of the dialog box and click OK
    – Here the example sets the financial year to start in April, you can change this by replacing the 4’s in the code to your financial year start month number.
if Date.Month([Date]) >= 4
 then Date.Month([Date]) - 4 + 1
 else Date.Month([Date]) + (12 - 4) + 1

Below is a list of M code you can use with the custom column button to create a range of non-standard date columns. Pick and choose which columns you want to add to your date table. For performance purposes, you should only create columns that you will be using in your report.

Month Number – calendar Month Number, Jan = 1, Feb = 2 etc

Date.Month([Date])

Month Short – short month name i.e Jan, Feb, Mar

Text.Start(Date.MonthName([Date]),3)

Month Letter – first letter of the month name

Text.Start(Date.MonthName([Date]),1)

Year Short – last 2 numbers of the year e.g 10 for the year 2010

Text.End(Number.ToText(Date.Year([Date])),2)

Month Year – short month name and year e.g Jan 2000

Text.Start(Date.MonthName([Date]),3)&" " & Number.ToText(Date.Year([Date]))

Year Month Number – year and month number (used to order months over years) i.e 200012 for Dec 2000

Number.ToText(Date.Year([Date]))&
Text.PadStart(Text.From(Date.Month([Date])),2,"0")

Financial Quarter – creates the quarter based on the financial year.
Here the example sets the financial year to start in April, change the
4’s in the code to the month number that your financial year starts at.

if Date.Month([Date]) < 4
 then "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - 4 + 1 + 12)/3))
 else "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - 4 + 1)/3))

Financial Year – creates the financial year, Here the example sets the financial year to start in April, change the 4’s in the code to the month number that your financial year starts at.

Date.Year(Date.AddMonths
(#date(Date.Year([Date]),
Date.Month([Date]),1),-(4-1)))

Financial Year Display – creates a user friendly financial year display e.g 2000-2001.
Here the example sets the financial year to start in April, change the 4’s in the code to the month number that your financial year starts at.

Number.ToText(Date.Year
(Date.AddMonths(#date(Date.Year([Date]),
Date.Month([Date]),1),- (4-1))))
&"-"& 
Number.ToText(Date.Year
(Date.AddMonths(#date(Date.Year([Date]),
Date.Month([Date]),1),+(12-4+1))))

Day of Week – day of week name e.g Monday

Date.DayOfWeekName([Date])

Day of Week Short – short day of week e.g. Mon, Tue, Wed

Text.Start(Date.DayOfWeekName([Date]),3)

Day of Week Letter – Shortened day of week e.g M for Monday Th for Thursday

if Date.DayOfWeekName([Date]) = "Monday" then "M" else
 if Date.DayOfWeekName([Date]) = "Tuesday" then "T" else
 if Date.DayOfWeekName([Date]) = "Wednesday" then "W" else
 if Date.DayOfWeekName([Date]) = "Thursday" then "Th" else
 if Date.DayOfWeekName([Date]) = "Friday" then "F" else
 if Date.DayOfWeekName([Date]) = "Saturday" then "Sa" else
 "S"

Day of Week Number – Number of the day of week where Monday = 1 and Sunday = 7. Change Day.Monday to the day you want your week to start. By default the first day is set as 0, so i’ve added a 1 to make the day numbers go from 1-7

Date.DayOfWeek([Date], Day.Monday)+1

Weekend – Sets the field to a Yes if the day is a Saturday or Sunday and No for all other days. If your weekends are Friday and Saturday change the code accordingly.

if Date.DayOfWeekName([Date]) = "Saturday" then "Yes" else
if Date.DayOfWeekName([Date]) = "Sunday" then "Yes" else "No"

Week Number – The week of year number, this example is where the week starts on a Monday. If you want the week to start on a different day change Day.Monday to the week start day.

Date.WeekOfYear([Date], Day.Monday)

STEP 4: SET THE TABLE AS A POWER BI DATE TABLE

Now you have created the completed date table you need to tell Power BI it is an official date table:

  1. Use the Close and Apply button under the Home menu to save your code and load the table into Power BI desktop
  2. Click on the table name in the fields panel (right hand side of screen)
  3. Click on the 3 dots that appear
  4. Select Mark as Date Table
  5. Select the date column from the drop down list

And there you have a completed date table ready to be used in Power BI. Check out my other date tutorials to learn how to use the date table:


Processing…
Success! You're on the list.

Cover Photo by Mille Sanders on Unsplash

Power Query M code for creating 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 of the screen, to something like Date Table or Calendar

  3. Next, copy the code into the empty query
    a. View menu Advanced Editor
    b. Paste the code below into the editor window.

    The code has a set of variables that can be changed to meet the requirements of your date table:
    Start – change the year in this variable to the year you wish your date table to start
    End – change the year in this variable to the year you wish your date table to finish
    alternatively you can change the dates to span whole financial years
    FinancialYearStartMonth – change to the month number your financial year starts in
    DayOfWeekStart – Change to the day the week starts on
    WeekendDay1 & 2 – Change to the days your weekend fall on

  4. Click Done
let
   // VARIABLES

   // add a start date (yyyy,m,d)
   Start= #date(2000,1,1),

   // add an end date (yyyy,m,d)
   End = #date(2023,12,31),

   // add the number of the month the financial year starts in
   FinancialYearStartMonth = 4,

   //Change to day your week starts at
   DayOfWeekStart = Day.Monday, 

   //Change WeekendDay 1 and 2 to the days your weekend fall on
   WeekendDay1 = "Saturday",  
   WeekendDay2 = "Sunday",

 DateList = List.Dates(
   Start, Number.From(End)- Number.From(Start)+1,
   #duration(1,0,0,0)),
      #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
      #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
      #"Inserted Day" = Table.AddColumn(#"Changed Type", "Day", each Date.Day([Date]), Int64.Type),
      #"Inserted Month Name" = Table.AddColumn(#"Inserted Day", "Month Name", each Date.MonthName([Date]), type text),
      #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
      #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", 
  each "Q"&Number.ToText(Date.QuarterOfYear([Date])), 
  Int64.Type),
      #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Financial Month Number", each if Date.Month([Date]) >= FinancialYearStartMonth
   then Date.Month([Date]) - FinancialYearStartMonth + 1
   else Date.Month([Date]) + (12 - FinancialYearStartMonth) + 1),
      #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
      #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month Short", each Text.Start(Date.MonthName([Date]),3)),
      #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month Letter", each Text.Start(Date.MonthName([Date]),1)),
      #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Year Short", each Text.End(Number.ToText(Date.Year([Date])),2)),
      #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Month Year", each Text.Start(Date.MonthName([Date]),3)&" " & Number.ToText(Date.Year([Date]))),
      #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Year Month Number", each Number.ToText(Date.Year([Date]))&
  Text.PadStart(Text.From(Date.Month([Date])),2,"0")),
      #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Financial Quarter", each if Date.Month([Date]) < FinancialYearStartMonth
   then "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - FinancialYearStartMonth + 1 + 12)/3))
   else "Q"&Number.ToText(Number.RoundUp((Date.Month([Date]) - FinancialYearStartMonth + 1)/3))),
      #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Financial Year", each Date.Year(Date.AddMonths
  (#date(Date.Year([Date]),
  Date.Month([Date]),1),-(FinancialYearStartMonth-1)))),
      #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Financial Year Display", each Number.ToText(Date.Year
  (Date.AddMonths(#date(Date.Year([Date]),
  Date.Month([Date]),1),- (FinancialYearStartMonth-1))))
  &"-"& 
  Number.ToText(Date.Year
  (Date.AddMonths(#date(Date.Year([Date]),
  Date.Month([Date]),1),+(12-FinancialYearStartMonth+1))))),
      #"Added Custom10" = Table.AddColumn(#"Added Custom9", "Day of Week", each Date.DayOfWeekName([Date])),
      #"Added Custom11" = Table.AddColumn(#"Added Custom10", "Day of Week Short", each Text.Start(Date.DayOfWeekName([Date]),3)),
      #"Added Custom12" = Table.AddColumn(#"Added Custom11", "Day of Week Letter", each if Date.DayOfWeekName([Date]) = "Monday" then "M" else
   if Date.DayOfWeekName([Date]) = "Tuesday" then "T" else
   if Date.DayOfWeekName([Date]) = "Wednesday" then "W" else
   if Date.DayOfWeekName([Date]) = "Thursday" then "Th" else
   if Date.DayOfWeekName([Date]) = "Friday" then "F" else
   if Date.DayOfWeekName([Date]) = "Saturday" then "Sa" else
   "S"),
      #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Day of Week Number", each Date.DayOfWeek([Date], DayOfWeekStart)+1),
      #"Added Custom14" = Table.AddColumn(#"Added Custom13", "Weekend", each if Date.DayOfWeekName([Date]) = WeekendDay1 then "Yes" else
  if Date.DayOfWeekName([Date]) = WeekendDay2 then "Yes" else "No"),
      #"Added Custom15" = Table.AddColumn(#"Added Custom14", "Week Number", each Date.WeekOfYear([Date], DayOfWeekStart)),
      #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom15",{{"Quarter", type text}})
  in
      #"Changed Type1"

STEP 4: SET THE TABLE AS A POWER BI DATE TABLE

Now you have created the completed date table you need to tell Power BI it is an official date table:

  1. Use the Close and Apply button under the Home menu to save your code and load the table into Power BI desktop
  2. Click on the table name in the fields panel (right hand side of screen)
  3. Click on the 3 dots that appear
  4. Select Mark as Date Table
  5. Select the date column from the drop down list

And there you have a completed date table ready to be used in Power BI. Check out my other date tutorials to learn how to use the date table:

How to order month chronologically in Power BI


Processing…
Success! You're on the list.

How to order months chronologically in Power BI

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!

First column contains the month name as it appears in your data
Second column is a number indicating the order the months should be sorted in
(so for financial year order April would be numbered 1)

STEP 2
Load the table into Power BI using the ‘Get Data’ button

STEP 3
Next you need to sort the month column in this new month lookup table:
1. navigate to the month table in the data tab
1. select the month column
2. go to the ‘sort column’ in the ‘column tools’ tab
3. select ‘month numb’ from the drop down list


STEP 4
Next set up a relationship join between the month lookup and the table containing the monthly data:
1. click on the ‘Manage relationships’ button in the ‘Home’ tab
2. click on ‘New’ to set up a new relationship
3. from the first drop down select the month look up table
4. from the second drop down select the table containing monthly data
5. highlight the columns in each table that contain the month names
6. OK


STEP 5
Now create your visual using the month name from the month look up table, instead of the month name column in your data table, and your months will be ordered chronologically, like this:


Processing…
Success! You're on the list.

Cover photo by Glen Carrie on Unsplash