Need Urgent help with Cumulative 7 Years Measure

[Srinivas904]

Hi,

The above measure is working fine 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 February 2025, the calculation should consider data from February 2018 to February 2025. However, it is currently calculating only from 2019 to 2025, which is incorrect.

Can someone help adjust the measure to ensure it correctly considers a full 7-year range? 

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

Cumulative Units Last 7 Years =

CALCULATE(

    [Units],

    FILTER(

        ALL(DIM_TIME),

        DIM_TIME[FIN_YEAR] >= MAX(DIM_TIME[FIN_YEAR]) - 6 &&

        DIM_TIME[FIN_YEAR] <= MAX(DIM_TIME[FIN_YEAR])

    )

)

Thanks

Srinivas

[sjoerdvn]

Hi Srinivas,
I was using TODAY() as end date becuase I do not have any information on your semantic model. I usually do not use TODAY(), but take the last date we actually have data for, that is usually at least one day earlier. So that could be something like:

VAR LastDate = CALCULATE(MAX(FACT_TABLE[TRANSACTION_DATE]),ALL())

So basically take the max date from the main fact table that has an active relationship with the date dimension.
Now replace TODAY() with LastDate.

[sjoerdvn]

try this

VAR ToDate = IF(TODAY()<MAX(Dim_TIME[FullDate]),TODAY(),MAX(Dim_TIME[FullDate]))
VAR FromDate = EDATE(ToDate, -84)
RETURN
CALCULATE([Units],ALL(Dim_TIME),Dim_TIME[FullDate] <= ToDate, Dim_TIME[FullDate] > FromDate)

[Srinivas904]

Hi @sjoerdvn ,

Thank you! The calculation is pretty close, and the prior years are unaffected. However, the current year's value is incorrect.

It should consider the period from February 2025 to February 2018, covering exactly 7 years. The expected value is 42,561, but I am seeing 42,142, which is slightly lower.

Could this discrepancy be due to the use of the 4-4-5 calendar? Are there any adjustments needed in the DAX calculation to ensure accuracy?

Thanks

Srinivas

[bhanu_gautam]

@Srinivas904 Try using

DAX
Cumulative Units Last 7 Years =
CALCULATE(
[Units],
FILTER(
ALL(DIM_TIME),
DIM_TIME[DATE] >= DATEADD(MAX(DIM_TIME[DATE]), -7, YEAR) &&
DIM_TIME[DATE] <= MAX(DIM_TIME[DATE])
)
)

[Srinivas904]

Hi @bhanu_gautam , Thanks for replying back, I tried ussing it, bu t i got this error

Parameter is not the corect type

can you please assit with this ?

Thanks

Srinivas