topic Re: Need Urgent help with Cumulative 7 Years Measure in DAX Commands and Tips

Need Urgent help with Cumulative 7 Years Measure

Srinivas904 — Thu, 27 Feb 2025 10:26:02 GMT

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

Re: Need Urgent help with Cumulative 7 Years Measure

bhanu_gautam — Thu, 27 Feb 2025 10:30:58 GMT

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])
)
)

Re: Need Urgent help with Cumulative 7 Years Measure

Srinivas904 — Thu, 27 Feb 2025 10:42:17 GMT

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

Re: Need Urgent help with Cumulative 7 Years Measure

sjoerdvn — Thu, 27 Feb 2025 14:32:05 GMT

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)

Re: Need Urgent help with Cumulative 7 Years Measure

Srinivas904 — Thu, 27 Feb 2025 16:28:10 GMT

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

Re: Need Urgent help with Cumulative 7 Years Measure

sjoerdvn — Fri, 28 Feb 2025 10:48:54 GMT

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.