Forum Discussion

Menvf's avatar
Menvf
Frequent Visitor
3 years ago

Last 4 years + YTD

Hi,

 

I am trying to add a filter to my visuals in a report.

I want to see the last 4 years + YTD.

 

If I choose last 5 calendar years I don't get current year.

If I choose last 5 years I don't get the full last year, but rather a rolling 12 months period.

 

This was an attempt that I tried. Can't understand what goes wrong. I tried just creating an empty table and see which values get 1, and 2023 isn't getting any values to it. What am I doing wrong?

 

Is YTD or Last 4 Yrs =

VAR vToday = TODAY ()

VAR vYearOffset = 4

VAR vCurYear = YEAR ( vToday )

VAR vStartYear = vCurYear - vYearOffset

VAR vResult = IF (YEAR ( MAX ( 'Date Dimension'[Date] ) ) >= vStartYear && MAX ( 'Date Dimension'[Date] ) <= vToday;

1;

0)

RETURN vResult

Need Help
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Menvf ,Try a measure like

     

    YTD Today +4 =
    var _min = date(year(today()) -4,1,1)
    var _max = today()
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max)) // assuming you are using date table or use date from table

    • Menvf's avatar
      Menvf
      Frequent Visitor
      3 years ago

      amitchandak thank you for your effort, however it still doesn't work even when I change the date reference.
      What is the [Net] reference in the calculate?