Adding Day of Year column to Date Table with Financal Years not aligning with year

[tylerspck]

Hello All,

I am trying to add a column to my dates table where I am adding day of the year, but based off another column with a different Financial/Sales year

[fhill]

Could you please post an example set of columns?  What determines the 'Start of Year' from the 'other column'?

[tylerspck]

The Start Date for the year is always changing due to retail business and wanting to compare holiday weeks to holiday weeks year after year. 2021 year started 1/3 and ends 1/1, 2020 started 12/29 and ended 1/2.

[tylerspck]

I have the typical date table and merged a custom sales/financial year table off of the date[date] column,  I just need to find a way to have day of year based off of the sales/financial year column since I never have a definitive start and stop every year

[fhill · Accepted solution]

Here is a Date Table and Non-Related FY (by year) Table

1. Based on DATE in DateTable, search the FY Start and End Columns to determine which FY Year each Date falls into....

FY_Search = CALCULATE( VALUES('Table (2)'[FY]),

FILTER('Table (2)', 'Date Table'[Date] > 'Table (2)'[FY Start] && 'Date Table'[Date] < 'Table (2)'[FY End]))

 2.  Now that we know each year, go lookup the FY Start Date again (or merge this and the next query together...)

FY_Start_lookup = LOOKUPVALUE('Table (2)'[FY Start], 'Table (2)'[FY],'Date Table'[FY_Search])

 3.  Now that we know the FY Start Date, we can just DateDiff from DATE to get days.

Date_Diff = DATEDIFF('Date Table'[FY_Start_lookup], 'Date Table'[Date],DAY)