Change in % calculation from previous year using custom fiscal week numbers

[Mohanad-Mustafa]

Hello Team,

I am really in a pickle and need help. 

I am trying to calculate percentage change year over year but using fiscal week numbers as we have a retail 4-4-5 custom calendar

that has different periods for months across different years. For instance, Jan 2025, cut off on Jan 25th and Jan 26th is considered a period in February, the formula below was working fine with a regular calendar year until we introduced this new custom 4-4-5 calendar, I do have fiscal week numbers, fiscal month number and fiscal year. Fiscal year starts normally from Jan-Dec. 

YOY% =

IF(

    ISFILTERED(Date_Dimension[Full_Date].[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Sales'[Total Sales]),

            DATEADD('Date_Dimension'[Full_Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM('Sales'[Total Sales]) - __PREV_YEAR,__PREV_YEAR)

)

Right now, what is happening is the change in percentage is comparing the days for Jan 2025 the same exact days in Jan 2024 which is not correct as Jan 2025 cuts off on Jan 25 and Jan 2024 cuts on on Jan 27.

I have tried changing the date parameters using all and datebetween and still same result, its always comparing the same days in a month among different years.

Really appreciate any assistance in here, thank you so much 

[Anonymous]

Hi @Mohanad-Mustafa,

Any update for this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

Regards,

Xiaoxin Sheng

[Mohanad-Mustafa]

Hello @Anonymous , unfortunately there was only solution proposed and didn't work, I just have a sales table with the following columns: accounts, sales, invoice date and then I have a custom 4-4-5 calendar with custom week numbers that is connected with the sales table one to many by date. For instance, week 5 starts on Jan 26th 2025 and ends on Feb 1st 2025 , so Jan 26th 2025 is practiaclly the first day of Feb 2025, then 2024 has different days in the months as well, I hope this clarify my situation to you a bit better 

[Mohanad-Mustafa]

Hi @danextian , can you weigh in here please? Thanks 

[rajendraongole1]

Hi @Mohanad-Mustafa  - Instead of using DATEADD, you should compare sales based on fiscal week numbers and fiscal year.Adjust the calculation to find the equivalent fiscal week from the prior year.

YOY% =
VAR CurrentSales = SUM('Sales'[Total Sales])
VAR CurrentYear = SELECTEDVALUE('Date_Dimension'[Fiscal Year])
VAR CurrentWeek = SELECTEDVALUE('Date_Dimension'[Fiscal Week Number])

VAR PrevYearSales =
CALCULATE(
SUM('Sales'[Total Sales]),
TREATAS(
{CurrentWeek},
'Date_Dimension'[Fiscal Week Number]
),
'Date_Dimension'[Fiscal Year] = CurrentYear - 1
)

RETURN
IF(
NOT ISBLANK(PrevYearSales),
DIVIDE(CurrentSales - PrevYearSales, PrevYearSales),
BLANK()
)

changed the logix with TREATAS function, please check and confirm.

[Mohanad-Mustafa]

Thanks for your input, I tried the measure but it doesn't return any result in the visual, the measure has no syntax error though, I tried to tweak it myself with no luck, any other suggestions please? @rajendraongole1 

[Anonymous]

Hi @Mohanad-Mustafa ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng