topic Minus years from date in filter in DAX Commands and Tips

Minus years from date in filter

coatsy35 — Mon, 02 Sep 2024 12:47:28 GMT

All,

I am using the folowing measure, which works well.

Measure = CALCULATE(SUM('Dead Stock [Stk Value]),'Dead Stock'[Last Purchased Year]<=MAX('Dead Stock '[Last Purchased Year]))

However, I wish to have this filter only show items that are 5 years old or more i.e. 2019 or below. I have tried various things but unable to get it to work with years. If I type days like below in I get it to work, how can I alter it to allow years?

Measure = CALCULATE(SUM('Dead Stock [Stk Value]),'Dead Stock'[Last Purchased Year]<=MAX('Dead Stock '[Last Purchased Year])-1825)

Re: Minus years from date in filter

Gabry — Mon, 02 Sep 2024 12:57:04 GMT

Hello coatsy35

I suppose that [Last Purchased Year] actually is a full date column and not a year column. So if it's full date you can just write year('Dead Stock'[Last Purchased Year])<=year('Dead Stock'[Last Purchased Year])-4

Re: Minus years from date in filter

coatsy35 — Mon, 02 Sep 2024 12:59:17 GMT

Gabry its actaully a column of year and not date

Re: Minus years from date in filter

Gabry — Mon, 02 Sep 2024 13:09:36 GMT

So how can it works with -1825? Is it a number? Please provide sample data

Re: Minus years from date in filter

Anonymous — Tue, 03 Sep 2024 02:29:22 GMT

Hi coatsy35 ,

Based on your description, I created this data.

You think that Last Purchased Year is just YEAR probably because it is formatted as yyyy, when in fact the essence is still the full date column.

So you can try what Gabry said and it will give you the correct results as well.

Alternatively, you could try creating a separate calculated column for year.

Year = YEAR('Table'[Last Purchased Year])

Then, use the calculated column to get the desired result.

Measure 3 = CALCULATE(SUM('Table'[Stk Value]),'Table'[Year] <= MAX('Table'[Year]) - 5)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Minus years from date in filter

coatsy35 — Tue, 03 Sep 2024 08:46:41 GMT

Yuo are quite right. I wasn't looking correctly at the format. Sorted now with Gabry answer. Thank you