Calendar in Power BI

[JoaoMarcelino]

When we perform data analysis, we quickly feel the need to frame it in a context of time evolution, for example, over periods of years, months, or even days. How can we do it?

First comes first: Power BI tries to help us with the “Time Intelligence” option (auto date/time for new files). As defined by Microsoft, when this option is turned on, “a hidden date table is created by default, for new files, for each field in the model that has a Date or Date type data”.

Power BI tries to help us with this option, but for performance reasons, it should be turned off (in Options -> Data Load -> Time intelligence -> turn off “Auto date/time for new files”).

So what's the alternative?

By following good modeling practices, the alternative we should choose is to create and use a Calendar dimension table that will link to and filter our fact table.

The Calendar table is a table that contains the listing of all the different days, between a start and an end date, but what makes it so special? In summary and according to the Reza Rad article “ Do you need a date dimension “, a Calendar table allows:

• Ability to “ slice and dice ” by various date attributes, such as weeks, semester, days of year, day type, etc;
• Analysis consistency and better performance;
• Ability to perform analysis based on specific days, such as holidays (such as holidays, weekdays, etc.);
• Certain tools and calculations are facilitated by using the calendar dimension.

There are several ways to get a calendar table (both in DAX and in Power Query), in this article we will focus on creating a calendar table in Power Query :

1- Copy the following code in attachment(code to obtain a simplified calendar)

2- Open Power Query: “Home” tab -> Transform data

 

3- Create “New blank query”: In Power Query -> “Home” tab -> New Source -> Blank Query.

4- Click on Advanced Editor : Delete the piece of code marked below

 

5 – Paste the code previously copied in step 1 and make the following changes:
5.1- Change steps P_StartDate and P_EndDate accordingly (example, if you want the start date to be 01/01/2020, in step P_StartDate replace the year 2018 for 2020; if you want the end date to be the 31/12 of the current year (dynamic) you can leave the P_EndDate unchanged; if you prefer, for example, that the end date of the calendar is the end of the year of the previous year up to date, simply insert “-1” after “Date.Year(P_Today)” – something like this: “Date.Year(P_Today)-1, 12, 31)”.

 

5.2 – A brief note about the language: this calendar is prepared for the translation into English or Portuguese of the names of the columns of the calendar table and of the rows, for example, from “Weekend” to “Fim de Semana”, with the manipulation of the step “P_Culture”:

• If P_Culture = “en-EN”, the entire calendar is in English;
• If P_Culture = “pt-PT”, the entire calendar is in Portuguese.

6- Rename the query to “Calendar ”: Double click on the query created, rename it to “Calendar” or “Calendar” and then, Home tab -> “Close and Apply”:

 

7- Create a relationship: Link the Calendar table to the fact table, using the “Date” field of each table.

These are the steps to start enjoying a complete calendar in your model, which allows all the desired analysis dynamism and performance.

In the next article, we'll show you how to import a calendar that also allows you to insert movable holidays or local holidays not included.

Hope you enjoyed it!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂

 

Simplified Calendar.zip

[Anonymous]

I have a query like below

if veeva remote meeting id <> blank then "Veeva",
if veeva remote meeting id = blank and call type contain is F2F then "F2F"
Else Tele calls 

Veeva Remote Meeting Id	Call Type	Flag/ Requrement
8906588459	Remote Detail	Veeva
1909341682	Remote  Sell & Detail	Veeva
5267800731	Remote Detail	Veeva
3286298179	Remote  Sell & Detail	Veeva
9372873410	Group Detail (F2F)	Veeva
	Phone Sell/Detail	Tele Calls
	Phone Sell/Detail	Tele Calls
	Phone Sell/Detail	Tele Calls
	Phone Sell/Detail	Tele Calls
	Phone Sell/Detail	Tele Calls
	Detail (F2F)	F2F Calls
	Detail (F2F)	F2F Calls
	Detail (F2F)	F2F Calls
	Detail (F2F)	F2F Calls
	Detail (F2F)	F2F Calls
	Detail (F2F)	F2F Calls