The ability to extract parts of a date is a fundamental building block of Power BI Dashboards. Power BI provides a number of ways to parse and modify dates. We’ll look how to extract parts of dates using Power Query and Power BI DAX Formulas and when you should consider one method over the other.
Extract a Year, Month, or Day from a Date in Power BI using the Power Query Editor or DAX formulas. The DAX formulas are: Year = Year([Date]) , Month = Month([Date]) or Day = Day([Date]). Power Query is a more efficient method to extract years, months and days in Power BI but requires more complex formulas.
Let’s take a look at the different methods and the requirements to extract dates!
Table of Contents
Why You Should Separate Years, Months, and Days from Dates in Power BI
While there are a lot of individual use cases that make sense to parse a date into its underlying pieces, two of the most common reasons we see are wanting to split dates into their own filters or slicers and splitting apart dates can make it easier to write subsequent DAX formulas or perform calculations.
In the screenshot at the beginning of the article, we showed a date split into 3 slicer sections. Slicers are filters in Power BI that users can modify within the dashboard to view data that they want to see.
While you have the ability to show a date range picker or individual days in Power BI, it can be advantageous to force users to view only a year and month at a time with no individual days, such as when you are presenting full month financial statements.
The second use case for calculations is that Power BI supports some relative date functions and quick measures for items like prior year same month, it can be easier to filter a measure using simple math, such as Year – 2 to subtract 2 years from a calculation.
Regardless of the specific use case, the first thing you will need to do is make sure that the Date Column you want to split is defined as a Date Data Type in Power Query.
The Importance of the Date Data Type in Power BI
Dates in Power BI are stored as a specific data type that recognizes them as points in time. The date data type is a requirement to perform many date calculations in Power BI. To determine whether a column is a date format, you can find a small Calendar icon at the top of each column in the Power Query Editor or to the left of a column name in the data panel of the Report View screen.
The example below shows Power Query Editor data types on the left, and the Report View screen on the right.
You will see the Date column is defined as a date data type.
If your data is not a date data type, open the Power Query Editor, Right Click on the column name and select a new data type.
Quick Tip: You can hold down shift and select multiple columns at a time before right clicking to update multiple column data types at one time.
How to Extract Years, Months or Days using Power BI DAX Formulas
DAX is the fastest way for Power BI developer to extract a year, month or day from a date column in Power BI. First ensure that the date column is a date data type. Click on the table you want to create the new field. Then select New Column and enter a DAX Formula.
Thew New Column button is found under the Table Tools section of the Power BI Ribbon.
After clicking New Column, a formula box will appear that you can type a DAX formula into.
The formulas are as follows:
Year = Year(TableName([Date])
Month = Month(TableName([Date])
Day = Day(TableName([Date])
After creating the DAX Formula, Power BI will generate the new field name and make it available as a drag and drop measure on the field list of your dataset. Try creating a measure table to keep track of custom calculations.
How to Extract Years, Months or Days using the Power Query Editor
When parsing dates in Power BI, using the Power Query editor is the preferred method. To parse a year, month, or day in Power Query, first launch the Power Query Editor. Click on the Custom Column button from the Add Column section of the Power Query Ribbon.
The syntax for the Power Query M Formulas are:
Year = Date.Year([Date])
Month = Date.Month([Date])
Day = Date.Day([Date]
Once created, the formulas will add a step to the Power Query transformations to generate new columns with the desired extracted data.
Alternative Option for Creating Power Query Formulas to Extract Years from Dates
Another option to extract periods from a data in Power Query is to use the Column From Examples function. It uses AI to detect patterns of a desired column outcome and will automatically generate the required Power Query Formulas. In many cases it’s faster than writing your own Power Query code.
Launch Column From Examples from the Add Column section of the Power Query Editor.
After the Column From Examples editor opens, you can type the desired column output on the far right. The top part of the editor will show the suggested Power Query Formula.
In the example below, we began typing in the year to extract from the Date Column and it recommends the Year from Date Power Query formula.
Press OK to accept the suggestion and Power Query will generate the new column to extract a year, month or day from a date column.
When to Use Power Query vs DAX to create New Columns
When determining when to use DAX or Power Query you have to keep the end user in mind, and understand the execution timing of each query or calculation. Power Query will calculate and create new columns when data is refreshed, while DAX will perform calculations in real-time. This means that extracting dates to create new columns could be done during a scheduled refresh using Power Query, while DAX will perform the new column creation as needed.
Because parsing a date is often a static exercise that will be applied to every row in Power BI, our recommendation is to front load the calculation as part of a Power Query transformation. However, we do recognize that there are times when the extraction can be part of a larger DAX formula or the dataset is so small that the end user performance impact will be minimal.
Conclusion
Power BI Developers can choose to write DAX formulas or use Power Query to extract years, months or days from a date. The first step is to ensure that a date is being read as a date data type. Next, the method used should be driven on when the developer desires the calculation to be performed.
Our preference is to build calculations for new date columns using the Power Query Editor. For a quick low code solution, users can use the Column From Examples functionality of Power Query to avoid having to remember which M formula to use.
Related Articles
- Calculate the Difference Between Two Dates in Power BI
- How to Calculate Percentage Change in Power BI
- How to Add a Search Box to Slicers and Dashboards in Power BI
- When to Use DAX vs Power Query in Power BI