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 ...
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 )
