Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JulianTobon
Helper I
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! 👨‍💻

ChandeepChhabra
Impactful Individual
Impactful Individual

@JulianTobon 

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

Phil_Seamark
Employee
Employee

Hi @JulianTobon,

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark 

 

Example

 Ejeplo.PNG

Hi @JulianTobon

 

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark, thanks for your reply.

 

The result was:

 

Captura.PNG

 

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.

 

 

HI @JulianTobon

 

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

Captura.PNG

 

In Power BI

Capturaas.PNG

 

@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

HI @Vvelarde 

 

This formula Did not give correct data

 

Captura.PNG

@JulianTobon

 

Ok, maybe your structure is different in my scenario. 

 

Hi, WeekFY.png




Lima - Peru
Sean
Community Champion
Community Champion

@VvelardeWhere's week 28?

Vvelarde
Community Champion
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.

 

WeekFY.png 




Lima - Peru
Sean
Community Champion
Community Champion

Yes no need to add +1 when adding the 2 numbers

@JulianTobondoes this work better?

 Fiscal Week.png

 

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.

 

Captura.PNG

@Sean No Smiley Sad

any development on this? ive got the same issue.

 

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

@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?

@JulianTobon Hi ,

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

thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.