Quickly create Week, Month, Quarter, and Year fields from a date using DAX in POWER BI

It’s rare to come across a report or data set that does not slice by time. While it’s necessary for virtually all reports, analysts often underestimate the difficulty of creating time slicers. Here is how, with one date field, you can use DAX in PowerPivot to extract week, month, quarter, and year fields.

I’m starting with the field [Send Date]. Its value is 1/1/2014 12:00:00 AM – this is the standard format for a date field.

WeekType this text into a new column:
And the result is “Week 01”. Note that, for weeks that only have one digit (Weeks 1 through 9), this formula uses RIGHT and CONCATENATE to add a 0 as padding, to ensure proper A-Z ordering.

The result is “01 Jan”. You can modify the results to show different Month labels (e.g. spell out “January”) using this documentation.

The result is “Q1”. Note that there is no built in formatting for Quarters (as there is for Months and Years), so we have to do our own math calculations here using the Month number.

Year=FORMAT([Send Date],”YYYY”)
Finally, an easy one! You could also use =YEAR([Send Date]), but this results in an integer field rather than a text field. Year is cleaner as a text field, as Excel may try aggregate/group the years by a mathematical function if they are integers.

Credits to: https://www.decisivedata.net/blog/quickly-create-week-month-quarter-and-year-fields-from-a-date-using-dax

Author: MahesH

Leave a Reply

Your email address will not be published. Required fields are marked *