cancel
Showing results for
Did you mean:
Regular Visitor

## Creating a fiscal week column

Trying to create a Fiscal week column from WeekNum in my Calendar table. I got the WeekNum done already.
Fiscal year starting on Week 40, so I am trying to create something like the one below:

I would include my Dax code if I had some...

 WeekNum Fiscal WeekNum 1 14 2 15 3 16 4 17 5 18 6 19 7 20 8 21 9 22 10 23 11 24 12 25 13 26 14 27 15 28 16 29 17 30 18 31 19 32 20 33 21 34 22 35 23 36 24 37 25 38 26 39 27 40 28 41 29 42 30 43 31 44 32 45 33 46 34 47 35 48 36 49 37 50 38 51 39 52 40 1 41 2 42 3 43 4 44 5 45 6 46 7 47 8 48 9 49 10 50 11 51 12 52 13

Any ideas ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Creating a fiscal week column

@MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.

It looks like you missed the if statement.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

9 REPLIES 9
Super User

## Re: Creating a fiscal week column

A little ugly, might have to account for 53 week years although that wouldn't be the end of the world.

```Fiscal Week =
VAR __fw = [WeekNum] - 40 + 1
RETURN IF(__fw<=0,52+__fw,__fw)```

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User

## Re: Creating a fiscal week column

@MrMarshall Just want to be clear on this - WeekNum column has Calendar Year WeekNumber isn't it ?

FiscalWeekNum will be the weeknumber starting from April to End of March. In that case how come, Calendar WeekNum 1 has FiscalWeekNum 14.

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Regular Visitor

## Re: Creating a fiscal week column

Thx for answer @Greg_Deckler,

I am afraid I didn't get the query to work the way it should have. I get the results below.

Regular Visitor

## Re: Creating a fiscal week column

Thx for anser @PattemManohar,
Yes, WeekNum is calendar Year Weeknumber.

The financial year is starting in October, hence on Week 40.

There for, WeekNum 40 should be Fiscal year 1.
And if we continue to count, Weeknum 52 should be Fiscal year 13, and Weeknum 1 should there for be 14.

Super User

## Re: Creating a fiscal week column

@MrMarshall Might be something like this you are looking for..

```FiscalWeekNum =
VAR _Step1 = IF(Test51FiscalWeekNum[WeekNum]<40,Test51FiscalWeekNum[WeekNum]+13,0)
RETURN IF(_Step1<>0,_Step1,RANKX(FILTER(Test51FiscalWeekNum,Test51FiscalWeekNum[WeekNum]>=40),Test51FiscalWeekNum[WeekNum],,ASC))```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Regular Visitor

## Re: Creating a fiscal week column

Hi! Thanks for the reply @PattemManohar,

It almost worked, but the Weeknumbers above 40 is going nuts:

 WeekNo FiscalWeekNum 38 51 39 52 40 1 41 218 42 435 43 652 44 869 45 1086
Super User

## Re: Creating a fiscal week column

@MrMarshall - Not sure what went wrong, take a look at this PBIX file attached. Table3.

It looks like you missed the if statement.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Creating a fiscal week column

That worked Greg!

There must be some culture error on my Power BI.

I changed:

```VAR __fw = [WeekNum] - 40 + 1
RETURN IF( __fw <= 0,52 + __fw , __fw )```

To:

```VAR __fw = [WeekNum] - 40 + 1
RETURN IF( __fw <= 0 , 52 + __fw , __fw )```

and it works after the change of spaces

Thanks!

Super User

## Re: Creating a fiscal week column

Weird! Glad you got it though!!

When in doubt, add blank lines and recompile...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements

#### How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 104 members 1,672 guests
Recent signins: