How can I calculate the year-over-year percentage change using custom fiscal week numbers in Power BI Desktop

Question

How can I calculate the year-over-year percentage change using custom fiscal week numbers in Power BI Desktop?

I'm working with a Power BI report that follows a custom fiscal calendar with week numbers instead of standard dates. I need to calculate the year-over-year (YoY) percentage change based on these custom fiscal weeks rather than calendar weeks. What is the best approach using DAX to compare data from the same fiscal week in different years? How can I ensure that the calculation correctly accounts for shifting fiscal weeks across years?

score 0 · Answer 1 · 3 days

In Power BI, to evaluate the YoY percentage variation through custom fiscal weeks, one needs to set up the proper fiscal calendar and use the DAX measure to compare different years within the same fiscal week.

Step 1: Create a Custom fiscal calendar table

Here's what your date table should include:

Date column

FiscalYear

FiscalWeekNumber (Week 1 to Week 52)

You can create a custom fiscal calendar in Power Query or DAX if you have not done so already.

Step 2: Create a Measure for YoY Comparison

You can use DAX to derive the current year's fiscal week value of the previous year:

PreviousYearValue = 
VAR CurrentFiscalWeek = SELECTEDVALUE(FiscalCalendar[FiscalWeekNumber])
VAR CurrentFiscalYear = SELECTEDVALUE(FiscalCalendar[FiscalYear])
RETURN 
CALCULATE(
    SUM(Sales[SalesAmount]),  
    FiscalCalendar[FiscalWeekNumber] = CurrentFiscalWeek, 
    FiscalCalendar[FiscalYear] = CurrentFiscalYear - 1
)

Step 3: Calculate the YoY Percentage Change

Now, create a DAX measure for YoY % change:

YoY Change % = 
VAR CurrentValue = SUM(Sales[SalesAmount])
VAR PreviousValue = [PreviousYearValue]
RETURN 
IF(NOT ISBLANK(PreviousValue), 
   DIVIDE(CurrentValue - PreviousValue, PreviousValue, 0)
)

Step 4: Provided Relationships and Filters Are Put on Properly

The Fiscal Calendar table should relate to the fact table (i.e., Sales data).

Instead of calendar week numbers, use the Fiscal Week Number in visuals.

Step 5: Verify the Working with the Table Visual

Put the fiscal year, fiscal week number, and YoY change % measure in a table. Ensure that the comparison accurately aligns fiscal weeks across different years.