Need Urgent help! on DAX: Adjustment Needed for Measure to Align with 4-4-5 Calendar

[Srinivas904]

Hi,

The measure is working correctly for prior years, but for the current year, it is incorrectly comparing the full year to -6 years. Instead, it should calculate exactly 7 years back.

For example, since we are in March 2025, the calculation should consider data from March 2018 to March 2025. However, it is currently calculating from 03/03/2025 to 03/03/2018, which is exactly 7 years. While this isn’t necessarily wrong, our custom 4-4-5 calendar defines March 2018 as starting from 26/02/2018.

The required adjustment is to ensure that the measure correctly calculates from the full March 2025 period back to the full March 2018 period, based on our 4-4-5 calendar structure.

Can someone help adjust the measure to align with this?

Just to confirm, we use the 4-4-5 calendar.

If you look at the data in the screenshot below, the value for 2025 should be 63,234, which is correctly selected from March 2018. However, in the screenshot above, the value for 2025 is incorrect.

If you look at the screenshot below, I have selected only 1803 (March 2018) while excluding data up to 05/03/2025, where the value is 845. However, when I unselect the date filter and check the screenshot below, the value is 965, which represents the entire month.

If we add 63,204 + 120, we get 63,324, which matches the value shown in the screenshot above.

this is the measure i am using the bar graph

Cumulative Units Last 7 Years =
VAR TodayDate = TODAY() -- Get today's actual date
VAR CurrentYear = YEAR(TodayDate) -- Extract the current year
VAR CurrentMaxDate = MAX(DIM_TIME[Full_Date]) -- Get the max date in context

-- Adjust the max date only if the report is showing the current year
VAR AdjustedMaxDate = IF(CurrentMaxDate > TodayDate && YEAR(CurrentMaxDate) = CurrentYear, TodayDate, CurrentMaxDate)

-- Calculate the start date (7 years before the adjusted max date)
VAR StartDate = EDATE(AdjustedMaxDate, -84)

RETURN
CALCULATE(
[Units],
FILTER(
ALL(DIM_TIME),
DIM_TIME[Full_Date] >= StartDate &&
DIM_TIME[Full_Date] <= AdjustedMaxDate
)
)

Thanks

Srinivas

[Srinivas904 · Accepted solution]

Hi All,

I actually found a solution for this but forgot to share it earlier. The measure below works perfectly for my requirement.

Essentially, we needed to track units for the last 7 years, comparing data from the current period going back exactly 84 months. Initially, the logic worked fine for past years, but now that we’re in 2025, the comparison was incorrectly using the entire calendar year (Jan–Dec) of 2025 against the last six full years — which doesn't align with the requirement.

However, with the new measure, it now calculates correctly — taking date-to-date logic into account. For example, as of 24th April 2025, it pulls data going back exactly to 24th May 2019, covering the last 84 months precisely which is 7 years.

Cumulative Units Last 7 Years NEW =

VAR TodayDate = TODAY()  -- Get today's actual date

VAR CurrentYear = YEAR(TodayDate)  -- Extract the current year

VAR CurrentMaxDate = MAX(DIM_TIME[Full_Date])  -- Get the max date in context

-- Adjust the max date only if the report is showing the current year

VAR AdjustedMaxDate = IF(CurrentMaxDate > TodayDate && YEAR(CurrentMaxDate) = CurrentYear, TodayDate, CurrentMaxDate)

-- Calculate the start date (7 years before the adjusted max date)

VAR StartDate = EDATE(AdjustedMaxDate, -84)  

RETURN

CALCULATE(

    [Units],

    FILTER(

        ALL(DIM_TIME),

        DIM_TIME[Full_Date] >= StartDate &&

        DIM_TIME[Full_Date] <= AdjustedMaxDate

    )

)

[bhanu_gautam]

@Srinivas904 , Try using

DAX
Cumulative Units Last 7 Years =
VAR TodayDate = TODAY() -- Get today's actual date
VAR CurrentYear = YEAR(TodayDate) -- Extract the current year
VAR CurrentMaxDate = MAX(DIM_TIME[Full_Date]) -- Get the max date in context

-- Adjust the max date only if the report is showing the current year
VAR AdjustedMaxDate = IF(CurrentMaxDate > TodayDate && YEAR(CurrentMaxDate) = CurrentYear, TodayDate, CurrentMaxDate)

-- Calculate the start date (7 years before the adjusted max date)
VAR StartDate = EDATE(AdjustedMaxDate, -84)

-- Adjust the start date to align with the 4-4-5 calendar
VAR AdjustedStartDate =
CALCULATE(
MIN(DIM_TIME[Full_Date]),
FILTER(
ALL(DIM_TIME),
DIM_TIME[Full_Date] >= StartDate &&
DIM_TIME[Full_Date] <= EOMONTH(StartDate, 0)
)
)

RETURN
CALCULATE(
[Units],
FILTER(
ALL(DIM_TIME),
DIM_TIME[Full_Date] >= AdjustedStartDate &&
DIM_TIME[Full_Date] <= AdjustedMaxDate
)
)

[Srinivas904]

Hi @bhanu_gautam ,

I tried it but the values didn't changed still same values. can you please help me on this ?

Thanks

Srinivas

[v-venuppu]

Hi @Srinivas904 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam for the prompt response.

The problem is that the current DAX measure doesn't fully account for the 4-4-5 calendar alignment, especially for the start and end of a given "month" based on that custom calendar.

The current logic just offsets by -84 months (i.e. 7 years back), but doesn’t align to the start of the custom period (like "March 2018" as per your 4-4-5 calendar). So, you end up misaligned totals when slicing across months.

Here are some steps to fix it:

• Use the Period ID or Fiscal Year-Month from your 4-4-5 calendar (DIM_TIME) to determine the current and 7-year-prior period.
• Align calculations to full periods using DIM_TIME[Period_ID] or whatever field denotes fiscal periods (like 1803, 2503, etc.).

Try using the below updated DAX Measure:

Cumulative Units Last 7 Fiscal Years =
VAR MaxDate = MAX(DIM_TIME[Full_Date])
VAR MaxPeriod = MAX(DIM_TIME[Period_ID]) -- assuming you have a custom field like 2503 for Mar 2025
VAR StartPeriod = MaxPeriod - 700 -- subtracting 7 years (you may need to adjust this logic based on how your Period_IDs are structured)

RETURN
CALCULATE(
[Units],
FILTER(
ALL(DIM_TIME),
DIM_TIME[Period_ID] >= StartPeriod &&
DIM_TIME[Period_ID] <= MaxPeriod
)
)

DIM_TIME[Period_ID] is something like 1803, 1903, etc., that identifies fiscal month-year. If it’s text or has a different format, we can adapt the subtraction logic using ranking or indexing.

If you don’t have numeric Period_IDs:
You can create a rank column in your DIM_TIME table using Power BI modeling:

PeriodRank = RANKX(ALL(DIM_TIME), DIM_TIME[Full_Date], , ASC, DENSE)
Then modify the measure like this:

Cumulative Units Last 7 Fiscal Years =
VAR MaxRank = MAX(DIM_TIME[PeriodRank])
VAR StartRank = MaxRank - 84 -- 7 years back (12 months * 7 years)

RETURN
CALCULATE(
[Units],
FILTER(
ALL(DIM_TIME),
DIM_TIME[PeriodRank] >= StartRank &&
DIM_TIME[PeriodRank] <= MaxRank
)
)

Make sure your DIM_TIME includes columns like:

• FiscalYearMonth (e.g. "2025-03")
• Period_ID (e.g. 2503)
• PeriodRank
• Validate that your time slicer is using those fiscal fields (not default Gregorian ones).

Cross-check with business definitions: ensure “March 2018” starts 26/02/2018 and ends accordingly.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 Thank you.