Forum Discussion

JulianTobon's avatar
JulianTobon
Helper I
9 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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! 👨‍💻