Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc

Hi everyone,

After diving into the amazing article (Understanding dateadd parameters with calendar-based-time-intelligence) I’ve finally pinpointed why some of my rolling 13-month averages were calculating incorrectly.

The default behaviour of some time intelligence functions are different from when using a classic vs custom calendar table. 

When using a custom calendar table, time intelligence functions like DATEADD or DATESBETWEEN default to Precise Mode. For example, if October 2025 has 26 days but October 2024 had 28 days, "Precise Mode" will truncate the shift. Instead of going to the end of the period in 2024, it stops at day 26 of the previous year and shift the hierarchy to include an extra month in the calculation.

To fix this, we can leverage the optional parameters in DAX time intelligence functions specifically designed for custom calendars. By switching the interval mode to ENDALIGNED, we force the function to always move to the last day of the period, regardless of how many days that month contains.

Updated DAX:

DATESINPERIOD (
            'Retail', 
            LastVisibleDate,
            -13,
            MONTH,
            ENDALIGNED
         )

I’ll be running more tests over the next few days to confirm all rolling averages are now aligning perfectly and will provide a final update next week.