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

day of year and day of month in Power BI using DAX

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”.

DAX expression to calculate day of year in Power BI

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
DAX expression to calculate day of quarter in Power BI

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)
calculate the number of days until the end of the quarter using DAX

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)
count the number of days until the end of the year using DAX in Power BI

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).

Day of month and day of week using DAX

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “Day of Year and Day of Quarter – DAX calculations for Power BI

  • Hello,

    My data spans several years and I would like to compare the yearly seasonality of my data. How do I include more than one year to the DAX formula without having many IF statements?

    Deedee

Leave a Reply

%d bloggers like this: