Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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! 👨💻
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/
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
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)
Hi @Phil_Seamark, thanks for your reply.
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.
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.
@Phil_Seamark My data skip dates, because not every day we receive purchases. Example Date/Calendar table:
In Power BI
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 )
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |