Rolling 12 month not working as intended - how to?

[Mjolnir]

Hi,

See image and DAX below. All dates are formatted as text YYYY-MM-DD in my data-sets, the BI report has slicers filtering dim_company, dim_year and dim_month. Correct values and months are returned in the table below, but incorrect year is being returned on the yellow-marked rows on the image, those should be 2023. I tried adding a filter on dim_year to my measure below, when doing that all values for 2023 disappears and only the ones related to 2024 are showing. Slicer [year] is set to 2024 but the rolling 12 measure should ignore this.

How can I tweak the measure to work as intended?

Costs rolling 12 =
VAR From_Date =
    FORMAT ( DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 ), "YYYY-MM-DD" )
VAR To_Date =
    FORMAT ( EOMONTH ( TODAY (), -1 ), "YYYY-MM-DD" )
RETURN
    CALCULATE (
        [Costs],
        dim_organization[company] = SELECTEDVALUE ( dim_organization[company] ),
        FILTER (
            ALL ( dim_calendar ),
            dim_calendar[date] >= From_Date
                && dim_calendar[date] <= To_Date
        ),
        FILTER (
            ALL ( dim_calendar ),
            dim_calendar[month] IN VALUES ( dim_calendar[month] )
        )
    )

[Mjolnir]

Thank you for your effort.

I have uploaded an excel to OneDrive, here you have it:

R12 sample

In case we don't come to a solution with the provided file, please let me know and I will do my best to share additional information with you.

Looking forward to your response.

[Mjolnir]

I edited the [year] slicer's interaction with the visual displaying the rolling 12 month and unabled the interaction and it works as intended (see image below).

The down-side with this is you can not only add the measure to visual content in your reports without unabling the interaction to the [year] slicer.

That said,if anyone has any suggestions on how to reqrite the DAX to get it working properly without this work-around, I would appreciate your input!

[Anonymous]

Hi @Mjolnir 

Actually that I can not understand your issue compeletely, your description missed some details. I can't help you very well.

Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.