How to get the last year value based on filter selection?

[Anonymous]

Hello,

Can anybody help me to get the last year's value based on the year selected on the filter? I am trying to create variance dynamically. Whenever a user selects a year it will reference by formula, (last year - current year )/ last year. But I am having a hard time getting the last year's value. It is coming blank. 

Current Year - 

Current_Year = 
    VAR CurrrentYear = CALCULATE([Line Amount (Formatted)], 
        FILTER('Calendar - Fiscal Date',
                'Calendar - Fiscal Date'[FiscalYr Year Number] = SELECTEDVALUE('Calendar - Fiscal Date'                         [FiscalYr Year Number])))
        return CurrrentYear 

Last Year - 

Last Year = VAR LastYear = CALCULATE([Line Amount (Formatted)], 
        FILTER('Calendar - Fiscal Date',
                'Calendar - Fiscal Date'[FiscalYr Year Number] = SELECTEDVALUE('Calendar - Fiscal Date'[FiscalYr Year Number]) -1))
        return LastYear

Variance- 

  VAR CurrrentYear = CALCULATE([Line Amount (Formatted)], 
        FILTER('Calendar - Fiscal Date',
                'Calendar - Fiscal Date'[FiscalYr Year Number] = SELECTEDVALUE('Calendar - Fiscal Date'                         [FiscalYr Year Number])))
    VAR LastYear = CALCULATE([Line Amount (Formatted)], 
        FILTER('Calendar - Fiscal Date',
                'Calendar - Fiscal Date'[FiscalYr Year Number] = SELECTEDVALUE('Calendar - Fiscal Date'[FiscalYr Year Number]) -1))
    return LastYear - CurrrentYear

My Data -  You can see below, last year is blank. Not sure why is that. 

Any help will be highly appreciated. 

Thank you 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

I'd like to sugggest you modify the measure as below.

measure =
VAR CurrrentYear =
    CALCULATE (
        [Line Amount (Formatted)],
        FILTER (
            ALL ( 'Calendar - Fiscal Date' ),
            'Calendar - Fiscal Date'[FiscalYr Year Number]
                = SELECTEDVALUE ( 'Calendar - Fiscal Date'[FiscalYr Year Number] )
        )
    )
VAR LastYear =
    CALCULATE (
        [Line Amount (Formatted)],
        FILTER (
            ALL ( 'Calendar - Fiscal Date' ),
            'Calendar - Fiscal Date'[FiscalYr Year Number]
                = SELECTEDVALUE ( 'Calendar - Fiscal Date'[FiscalYr Year Number] ) - 1
        )
    )
RETURN
    LastYear - CurrrentYear

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

This works. Thank you so much for your help. 

[Anonymous]

Hi,

Your logic is correct and the function I think is correct as well. The only thing when I try to update is 

FILTER( All 'Calendar - Fiscal Date',

After I add "All", it's working fine for me. Thanks!
 

[amitchandak]

@Anonymous , Try to use time intelligence functions for that

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) // change end date based on FY
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thank you, but this is not what I want. I want when I select 2019 o filter, I want to get 2018 values. I am not sure how many of the functions give me that. 

Any idea how I can get the last year based on filter value? Hopefully, this makes sense. 

Thank you