FY week number column

DAX DAX Calculations
1

Hi,

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

Enterprise%20DNA%20Expert%20-%20Small

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

Enterprise%20DNA%20Expert%20-%20Small

6

Check out the updated data table code here

There’s plenty of examples available for Power Query here also

Sam

1 Like