DAX All of a Sudden Comparing New Year Against Full Year LY

[L1102]

Hi All,

Backgroud: my reports have been reporting YOY calulations correctly across 3 years worth of time up until the new year rolled in.

Below is my set up.

I have a Fiscal Calendar Table as per below.

I have a fiscal calendar set up referencing to the fiscal calendar table as per below.

I've marked my table as a date table

Lastly I have my calculated groups set up for prior year as per below.

However, my PY & YOY calculations are not working with year 2026, but will work with 2025, 2024 & 2023

Hoping someone can figure out what I did wrong.

Your help is greatly appriciated.

[v-tejrama]

Hi @L1102 ,

Thanks for reaching out to the Microsoft fabric community forum

What you’re seeing is expected and isn’t due to changes in your DAX or calculation groups. The difference in behavior between previous years and the new year is related to filter context. In your visuals, the context for 2026 includes only the year, and since there are no fact rows for that year yet, DAX time intelligence functions like SAMEPERIODLASTYEAR or DATEADD can’t operate as intended.

These functions need actual dates to work, so when there’s no data for the year, the calculation returns blank or unexpected results. To resolve this, make sure your measures use a real date context such as date or year-month from your calendar table so DAX has a valid range of dates. If you want to compare a partial or empty current year to a previous year, the measure should anchor on the last available date in your fact table rather than relying on automatic time intelligence. With a valid date context, calculations will work as expected, and there’s no issue with your model or calculation groups.

Thank you,

Tejaswi.

[L1102 · Accepted solution]

The solution for my specifict set up is as per below.

PY =

VAR MeasureName = SELECTEDMEASURENAME()

VAR IsPriceMeasure = CONTAINSSTRING(MeasureName, "Price") || CONTAINSSTRING(MeasureName, "Avg")

RETURN

IF(

    IsPriceMeasure,

    CALCULATE(

        SELECTEDMEASURE(),

        'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1

    ),

    SUMX(

        VALUES('Fiscal Calendar'[Week Number]),

        VAR CurrentWeek = 'Fiscal Calendar'[Week Number]

        RETURN

            CALCULATE(

                SELECTEDMEASURE(),

                'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1,

                'Fiscal Calendar'[Week Number] = CurrentWeek

            )

    )

)

Abs Chg =

VAR MeasureName = SELECTEDMEASURENAME()

VAR IsPriceMeasure = CONTAINSSTRING(MeasureName, "Price") || CONTAINSSTRING(MeasureName, "Avg")

RETURN

IF(

    IsPriceMeasure,

    CALCULATE(SELECTEDMEASURE()) -

    CALCULATE(SELECTEDMEASURE(), 'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1),

    SUMX(

        VALUES('Fiscal Calendar'[Week Number]),

        VAR CurrentWeek = 'Fiscal Calendar'[Week Number]

        VAR _Current = CALCULATE(SELECTEDMEASURE())

        VAR _PY = CALCULATE(

            SELECTEDMEASURE(),

            'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1,

            'Fiscal Calendar'[Week Number] = CurrentWeek

        )

        RETURN _Current - _PY

    )

)

% Chg =

VAR MeasureName = SELECTEDMEASURENAME()

VAR IsPriceMeasure = CONTAINSSTRING(MeasureName, "Price") || CONTAINSSTRING(MeasureName, "Avg")

RETURN

IF(

    IsPriceMeasure,

    DIVIDE(

        CALCULATE(SELECTEDMEASURE()) - CALCULATE(SELECTEDMEASURE(), 'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1),

        CALCULATE(SELECTEDMEASURE(), 'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1)

    ),

    IF(

        HASONEVALUE('Fiscal Calendar'[Week Number]),

        VAR CurrentWeek = MAX('Fiscal Calendar'[Week Number])

        VAR _Current = SELECTEDMEASURE()

        VAR _PY = CALCULATE(

            SELECTEDMEASURE(),

            'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1,

            'Fiscal Calendar'[Week Number] = CurrentWeek

        )

        RETURN DIVIDE(_Current - _PY, _PY),

        VAR TotalCurrent = CALCULATE(SELECTEDMEASURE())

        VAR TotalPY =

            SUMX(

                VALUES('Fiscal Calendar'[Week Number]),

                VAR CurrentWeek = 'Fiscal Calendar'[Week Number]

                VAR _PY = CALCULATE(

                    SELECTEDMEASURE(),

                    'Fiscal Calendar'[Year] = MAX('Fiscal Calendar'[Year]) - 1,

                    'Fiscal Calendar'[Week Number] = CurrentWeek

                )

                RETURN _PY

            )

        RETURN DIVIDE(TotalCurrent - TotalPY, TotalPY)

    )

)

[v-tejrama]

Hi @L1102 ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[v-tejrama]

Hi @L1102 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Jihwan_Kim]

Hi,

I am not sure if I understood your data model correctly, but I think, there is only year-level-filter-context in the shown-visualizations in the screenshot.

Please try to add date-level-filter-context into the page or visuals or [Sales] dax measure, and then, once the filter context describes without the future date that do not have data in the year 2026, I guess it will work as expected.

I tried to create simple-sample-pbix file like below, and please have a look. The file is attached.

I did not try to create calculation group, but I tried to add date-level-filter-context to the measure.

Other method might be, adding it to the calendar table itself as binary column. 

[L1102]

This solution didn't work for me. I was able to figure out my own DAX that worked with my current Model set up with all my tables and relationships.