FY week number column


#1

Hi Sam,

How do I create a FY week number column using DAX. For example week number to start in July. I have watched all the videos, however I cannot find FY week column.

Thanks in advance.

Cheers
Elizabeth


#2

Make sure you have your date table setup correctly first. Here is a video from Sam, and a document with the M code you will need.Power BI Date Table code.txt (2.8 KB)
https://www.youtube.com/watch?v=KLfOOIAqfkQ&t=91s

Then you will need to create a column for Fiscal Day that looks like this:
Fiscal Day =
‘Calendar’[Date]
- DATE ( ( ‘Calendar’[Fiscal Year] - 1 ), 7, 1 ) + 1

Then create a column for Fiscal Week that looks like this:
Fiscal Week =
ROUNDUP ( ‘Calendar’[Fiscal Day] / 7, 0 ) - DATE ( ( ‘Calendar’[Fiscal Year] - 1 ), 7, 1 ) + 1

Thanks
Jarrett


#3

Hi Jarrett,

There is no fiscal year in the date table. Are you able to please provide instructions again.

Cheers
E


#4

Hi Jarrett,

I worked out how to create a financial Year, then tried the formulas you provided above and received and error. Are you able to please explain with an example.

Elizabeth


#5

Send me a copy of the error you received and a copy of the PBIX file if possible so I can better answer your question.

Thanks
Jarrett