Forum Discussion
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
Hi JulianTobon,
Do you have a Date table that contains a column showing which dates belong to which Fiscal year?
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)
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
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! 👨💻
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.
