Day of Year and Day of Quarter – DAX calculations for Power BI

Power BI has some built-in, easy-to-use DAX functions to get the Day of Month and Day of the week, but nothing for Day of Year and Day of the quarter. These calculations, however, are very simple and easy to implement using other functions. This short blog and video explain a method to calculate those for Power BI.

Day of Year

One simple way of calculating Day of Year is to get the date difference of that date with the starting day of that year. Here is how it works:

Day of Year = DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY)+1

This code will give us the day number of year for a give date. ‘Date'[Date] means the column named “Date” under the table named “Date”.

Day of Quarter

You can use the same method and get the date difference of the given date with the starting date of that quarter.

Day of Quarter = DATEDIFF(STARTOFQUARTER('Date'[Date]),'Date'[Date],DAY)+1

Days until the end of the quarter

If you compare with end of the quarter, then you get the number of days remained from the quarter.

Days until the end of the quarter = 
DATEDIFF('Date'[Date],ENDOFQUARTER('Date'[Date]),DAY)

Days until the end of the year

The calculation for counting the number of days until the end of the year is as below;

Days until the end of the year = 
DATEDIFF('Date'[Date],ENDOFYEAR('Date'[Date]),DAY)

Day of Month and Day of Week

For the day of Month and Day of week, you don’t need a custom expression, the DAY function will give you the day of month, and WeekDay will give you the day of week (with the ability to set the start date of the week).

Power Query Functions

These calculations can be done also in Power Query. If you are using the calculation in a dynamic DAX measure, then the calculations above would help. But if you need them to be added as a column or a pre-calculated expression, then I suggest doing it in Power Query.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.