Showing results for 
Search instead for 
Did you mean: 
Not applicable

Day number of year

Trying to find the syntax for to return a numer out of 1-365 for what number of day in year it is vs just knowing the number in the month  




You can also add a calcualted column in DAX.


DayNoOfYear =
DATEDIFF ( DATE ( YEAR ( 'Table'[Date] ), 1, 1 ), 'Table'[Date], DAY ) + 1


View solution in original post

New Member

Not seeing Date.Dayofyear coming up in any function within BI

Just use DAX

Helper I
Helper I

Just an additional application: I need to create a day number for a harvest year from 1-Jul till 30-Jun. Using solution from this query I did the following:


HarvYearDay = IF( 
            MONTH( DateTable[Date] ) > 6, 
                DATEDIFF( DATE ( YEAR ( DateTable[Date] ), 7, 1 ), DateTable[Date], DAY ) + 1, 
                DATEDIFF( DATE ( YEAR ( DateTable[Date] ), 1, 1 ), DateTable[Date], DAY ) + 185


This is just to share if someone needs to do the same.

Solution Sage
Solution Sage

Check out the Date.DayOfYear formula. You would use this in the query editor to add a new column, either as a custom column using the formula, or the simple way through the UI: select your date column, then to go the Add Column tab in the ribbon and use the drop-down on Date to select Day > Day of Year.



You can also add a calcualted column in DAX.


DayNoOfYear =
DATEDIFF ( DATE ( YEAR ( 'Table'[Date] ), 1, 1 ), 'Table'[Date], DAY ) + 1




I was able to use this solution to calculate the day number of the year. I was wondering if anybody had any ideas on how to calculate this for a fiscal year and account for the leap year?


My organizations fiscal year starts in October. I was able to use the DAX formula below to calculate the fiscal day number of the year but when leap years occur the formula does not calculate correctly. 


FiscalDayoftheYear = IF('Date'[DayofhtheYear]>273,'Date'[DayofhtheYear]-273,'Date'[DayofhtheYear]+92)


If anyone has any ideas that would be great! Thanks!

Not applicable

Did you ever digure it out? Got the same problem.

I have to calculate Day number of Quarter and Day number of Month help me please.

Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.