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:
=CONCATENATE(“Week “,RIGHT(CONCATENATE(“0”,WEEKNUM([Send Date])),2))
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.
Month=CONCATENATE(CONCATENATE(FORMAT([Send Date],”MM”),” “),FORMAT([Send Date],”MMM”))
The result is “01 Jan”. You can modify the results to show different Month labels (e.g. spell out “January”) using this documentation.
Quarter=CONCATENATE(“Q”,ROUNDUP(MONTH([Date])/3,0))
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
