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

Question

I want to get the last year value-based no filter value I selected. For example, if I selected 2019 on the filter, I want to get 2018 values (See picture below). I have a formula, but it will give me blank, Not sure whats wrong with. Can anybody please help me to get this? (Right now I am getting nothing, just blank)

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

My Data

Thank you so much

did you checked DAX function PREVIOUSYER? more details- docs.microsoft.com/en-us/dax/previousyear-function-dax — mkRabbani
@mkRabbani, I tried. I am not sure how to reference not year on the filter. Any idea? Thank you — Suneth
Do you have any "Date" column in table 'Calendar - Fiscal Date'? that case you can use that column for referencing PREVIOUSYEAR. — mkRabbani

mkRabbani mkRabbani · Accepted Answer · 2020-11-12T04:47:17

Did you checked DAX function PREVIOUSYER? You can check more details Here

You can also try this below adjusted measure-

Last Year = 

VAR LastYear = SELECTEDVALUE('Calendar - Fiscal Date'[FiscalYr Year Number]) -1

VAR LastYearValue = 
CALCULATE(
    [Line Amount (Formatted)], 
    FILTER(
        ALL('Calendar - Fiscal Date'),
        'Calendar - Fiscal Date'[FiscalYr Year Number] = LastYear 
                
    )
)

return LastYearValue

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

1 Answers