Forum Discussion

coatsy35's avatar
coatsy35
Icon for Helper II rankHelper II
2 years ago
Solved

Minus years from date in filter

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)
 
  • Gabry's avatar
    Gabry
    2 years ago

    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

5 Replies

  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    2 years ago

    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

      • Gabry's avatar
        Gabry
        Icon for Super User rankSuper User
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • coatsy35's avatar
      coatsy35
      Icon for Helper II rankHelper II
      2 years ago

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