Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

Forum breadcrumbs - You are here:ForumTips & Tricks: Power BICalendar Slicer
You need to log in to create posts and topics.

Calendar Slicer

How to create an actual calendar in a slicer in Power BI?

If you want to show that's happened before/after a specific time in the past/future (month, year, and so on) you can use a data slicer. When you refresh the time period, the data automatically applies the appropriate period.

First, you should create new calendar. The first step in creating your own Power Query is to add a blank query to Power BI. To create a new calendar, click on „Edit Queries“, then in „New Source“ choose „Blank Query“ and click on the „Advanced Editor“ button.

Blank Query

 

 

 

           

 

 

 

 

To add the procedure, delete the existing let expression and copy the following expression:

let

Source = List.Dates,
#"Invoke function" = Source(#date(2016, 01, 01), 1500, #duration(1, 0, 0, 0)),

#"List to table" = Table.FromList(#"Invoke function", Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),

#"Day Added" = Table.AddColumn(Date, "DayNo", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),

#"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"ddd","en-US")),
#"Month No Added" = Table.AddColumn(#"Day Name Added", "Month No", each Date.Month([Date])),

#"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMM","en-us")),

#"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),

#"EUROPEAN Week No added" = Table.AddColumn(#"Quarter No Added", "WeekNo", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])+1),2,"0")),

#"Year Added" = Table.AddColumn(#"EUROPEAN Week No added", "Year", each Date.Year([Date])),

#"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each Number.ToText([Year])&"-"&[Month Name]),
#"Year Week Added" = Table.AddColumn(#"Year Month Added", "Year-Week", each Number.ToText([Year])&"-"&[WeekNo]),

#"Year Quarter Added" = Table.AddColumn(#"Year Week Added", "Year-Quarter", each Number.ToText([Year]) & "Q"& Number.ToText([Quarter No],"00")),

#"Change type to text" = Table.TransformColumnTypes(#"Year Quarter Added",{{"Year", Int64.Type}, {"Date", type date}, {"Month No", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", Int64.Type}, {"Year-Quarter", type text}, {"Year-Month", type text}, {"WeekNo", Int64.Type}}),

#"Sort DayName" = Table.AddColumn(#"Change type to text", "SortDayName", each Date.DayOfWeek([Date],1)),

#"Sort YearMonth" = Table.AddColumn(#"Sort DayName", "SortYearMonth", each Number.ToText([Year])&Text.PadStart(Number.ToText([Month No]),2,"0")),
#"Sort YearWeek" = Table.AddColumn(#"Sort YearMonth", "Sort YearWeek", each Number.ToText([Year])&Text.PadStart(Number.ToText([WeekNo]),2,"0")),

#"Sort YearQuarter" = Table.AddColumn(#"Sort YearWeek", "SortYearQuarter", each Number.ToText([Year])&Text.PadStart(Number.ToText([Quarter No]),2,"0")),
#"Removed Columns" = Table.RemoveColumns(#"Sort YearQuarter",{"SortYearQuarter"})
in
#"Removed Columns"

 In the fourth row you can change the start date to another and change the amount of days, then just click on „Done “.

To rename the Query, right-click the query in the Queries pane, click Rename, type the name (for example Calendare, Date, and so on), and press Enter.

After you should create a relationship. Click on „New“ on the Manage Relationships.

In the "Create Relationship" dialog select a new table with the dates, and then select the column you want to use in the relationship. In the second table, select the other table you want in the relationship, then select the other column you want to use, and then click OK.

The calendar is ready, and you can create a slicer with the years/quarters/

Have a nice day!