Apply automatic date/time in Power BI Desktop

[bitheerani319]

This article is intended for data modelers who are developing Import or Composite models in Power BI Desktop. It introduces and describes the Automatic date/time option .

Auto Date/Time is a data loading option in Power BI Desktop. The purpose of this option is to support convenient time bolivia mobile database reporting based on date columns loaded into a model. Specifically, it enables report authors to use their data model to filter, group, and drill down using calendar periods (years, quarters, months, and days). Importantly, you don't need to explicitly develop these time intelligence capabilities.

When the option is enabled, Power BI Desktop creates an automatic hidden date/time table for each date column, as long as all of the following conditions are true:

Table storage mode is Import
The column data type is date or date/time
The column is not the "many" side of a model relationship
How it works
Each automatic date/time table is actually a calculated table that generates rows of data using the DAX CALENDAR function . Each table also includes six calculated columns: Day , MonthNo , Month , QuarterNo , Quarter , and Year .

Observation

Power BI translates and formats column names and values ​​according to the language of the model . For example, if the model was created using English, it will still show month names and so on in English, even if it is viewed with a Korean client.

Power BI Desktop also creates a relationship between the Date column of the automatic date/time table and the date column of the model.

The auto date/time table contains the full calendar years that span all date values ​​stored in the model's date column. For example, if the earliest value in a date column is March 20, 2016, and the latest value is October 23, 2019, the table contains 1,461 rows. This represents one row for each date in the four calendar years 2016 through 2019. When Power BI refreshes the model, each auto date/time table is also refreshed. This way, the model will always contain dates that span the values ​​in the date column.