math - Change in % calculation from previous year using custom fiscal week numbers - Stack Overflow

admin管理员组

文章数量:1414908

I need help with creating a working dax formula in Power Bi desktop.

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. Here is the dax below that I tried and works only well with a regular gregorian calendat year.

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.

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.

本文标签： mathChange incalculation from previous year using custom fiscal week numbersStack Overflow