cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Function To Calculate Fiscal Week

Hi all,

I want to calculate the number of weeks based on the date of sale, I used the formula:

week = WEEKNUM(Scorecard[SalesDate],2)

This works very well but not for what I need, Due to work with fiscal year sales date, example Fiscal Year 2016 starts in July 2015 and ends in June 2016, while Fiscal Year 2017 starts in July 2016 and ends in June 2017. So week 27 that is July-1 should be week 1 and the last week of June should be 53. I hope your help, thank you very much

22 REPLIES 22
Anonymous
Not applicable

Saw some incredibly complex solutions, a couple a bit simpler.  Wanted to try and provide a simpler approach for those who may be struggling.

The solution assumes you've created a DateDim table (how-to), and, that the table has the fields referenced, such as Year, Month, Day, etc.  All date references are CY unless specified Fiscal. In this example, the fiscal calendar begins on April 1st (4/1/####).

if #date([Year],[Month],[Day]) >= (#date([Year],4,1))
then
([Week of Year] - ((Date.WeekOfYear(#date([Year],4,1))))) +1
else
(Date.WeekOfYear(#date([Year],12,31)) - Date.WeekOfYear(#date([Year],4,1))) + [Week of Year]

The initial IF statement ensures the condition of a FY starting mid-week is properly handled.

Anonymous
Not applicable

Hey!

Managed my way through this problem with the formula @Vvelarde provided, with some adjustments that helped me to adjust the Fiscal Year start date to 1st of October, instead of July, and also, making it functional for calendar data sets that have various Fiscal Years, not just 1.

Also, for making weeks to start on another day such as Saturday or Monday, you should adjust that from the Calendar Week column you will use. What you could do is to create a dummy week column that starts in the day you need your fiscal week to start in and use that dummy column as a parameter for the following function (which is my solution):

WeeKFY2 =
VAR WeekStartinFY =
WEEKNUM ( DATE ( Calendario[FiscalYear], 10, 1),1)
RETURN
IF (
Calendario[Week] < WeekStartinFY,
WeekStartinFY + Calendario[Week] - 27,

IF(Calendario[Week] = WeekStartinFY && Calendario[FiscalYear] = Calendario[Calendar Year],
WeekStartinFY + Period[Week of Year]- 27,

Period[Week of Year] - WeekStartinFY + 1)

)

As I said, I need my Fiscal Year to Start on October, so the month Parameter would be 10 instead of 7 in the variable WeekStartinFY.  When that change is done, in the cases where the variable "WeekStartinFY" is less than (<) the "Calendario[Week]", the number to subtract would be 27 instead of 1.

Another validation that needs to be done in order for the method to work through a multiple fiscal years table, would be for when the "WeekStartinFY" and the "Calendario[Week]" are the same (this happens always in the first and last week of each fiscal year). The method I found for differentiating which week belongs to which fiscal year, would be through the normal Calendar Year value.

So, add a new column that stores the normal Calendar Year value. And that would work for the following validation:

IF(Calendario[Week] = WeekStartinFY && Calendario[FiscalYear] = Calendario[Calendar Year]

That allows the calculus to work properly on tables that store multiple fiscal years.

Hope someone find this useful! 👨‍💻

Impactful Individual

I have recently written a blog post to customize the fiscal week. Please check it out here - https://www.goodly.co.in/calculate-fiscal-week-in-power-bi/

• You'll have the option to customize the fiscal year start month.
• And starting day of the week - Eg. mon, tue etc..

Just copy the DAX code and create a new column in your date table and paste it there! It should work fine!

Let me know..thanks

Microsoft Employee

Hi @JulianTobon,

Do you have a Date table that contains a column showing which dates belong to which Fiscal year?

Proud to be a Datanaut!

Helper I

Example

Microsoft Employee

This might be getting close....

FY Week Num = IfERROR(DATEDIFF(

CALCULATE(
FIRSTDATE('FiscalSales'[Sales Date]),
FILTER(
ALL('FiscalSales'),
'FiscalSales'[Fiscal Year] = EARLIER('FiscalSales'[Fiscal Year])
&& 'FiscalSales'[Sales Date] <= EARLIER('FiscalSales'[Sales Date])
)
),'FiscalSales'[Sales Date],WEEK)+1,-1)

Proud to be a Datanaut!

Helper I

The result was:

As you can see there are calendar weeks that have 2 different fiscal weeks and this should not be so .. for example calendar week 52 should only be fiscal week 26.

Microsoft Employee

Should be easy enough to fix.

Do you have a Date/Calendar table where you have 1 row per day?  I notice your sample data seems to skip lots of days.

Proud to be a Datanaut!

Helper I

@Phil_Seamark My data skip dates, because not every day we receive purchases. Example Date/Calendar table:

In Power BI

Community Champion

@JulianTobon

Hi, please try this calculated column in your table and let me know

WeeKFY2 =
VAR WeekStartinFY =
WEEKNUM ( DATE ( Calendario[FiscalYear], 7, 1 ), 2 )
RETURN
IF (
Calendario[WeeK] < WeekStartinFY,
WeekStartinFY + Calendario[WeeK]
+ 1,
Calendario[WeeK] - WeekStartinFY
+ 1
)

Lima - Peru
Helper I

This formula Did not give correct data

Community Champion

@JulianTobon

Ok, maybe your structure is different in my scenario.

Hi,

Lima - Peru
Community Champion

@VvelardeWhere's week 28?

Community Champion

@Sean

Yes, the formula need some adjustment.

WeeKFY2 =
VAR WeekStartinFY =
WEEKNUM ( DATE ( Calendario[FiscalYear], 7, 1 ); 2 )
RETURN
IF (
Calendario[WeeK] < WeekStartinFY,
WeekStartinFY + Calendario[WeeK],
Calendario[WeeK] - WeekStartinFY
+ 1
)

Now Appears.But don't know if this the solution to Julian's Question.

Lima - Peru
Community Champion

@JulianTobondoes this work better?

Helper I

We are on the right track, but as you can see an error is generated at the calendar year change. For example is January 1 should be week 27 and the formula detects it as 28 and changes the next day to 29.

Helper I

@Sean No

Frequent Visitor

any development on this? ive got the same issue.

trying to create a column for Fiscal Week 1-53, starting on July 1st.

Helper I

@Phil_Seamark excuseme, Your form is a little more successful, what I can detect in it is that it starts the count of the week on Sunday, but it should start on Monday. We have to indicate that in the Formula?

Regular Visitor

@JulianTobon Hi ,

Please share the solution if you have the solution for the fiscal week starts with monday

thanks

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.