最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin0浏览0评论

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.

发布评论

评论列表(0)

  1. 暂无评论