CALCULATE returns blank values

[sugloevg]

Hi!

I have a simple measure but i can't get why it doesnt work.

For example i have table:

Year	LSV
2018	1235
2019	5432
2020	7653

So i need to show value by the previous year in a row. I made the following measure:

PREVLSV = CALCULATE(SUM('Table'[LSV]), FILTER('Table', 'Table'[Year] = [Year]-1))

and it returns blank values for all rows:

Is there ant chance to get it works?

[Syndicate_Admin]

Try this @sugloevg

Prior Year = 
VAR PriorYear = MAX('Table'[Year]) - 1
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Year] = PriorYear
        ),
        'Table'[LSV]
    )
RETURN
    Result

Gets the current year, and then subtracts 1. Then, the SUMX() only works on the previous year's data from the table provided by FILTER().

[Syndicate_Admin]

Try this @sugloevg

Prior Year = 
VAR PriorYear = MAX('Table'[Year]) - 1
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Year] = PriorYear
        ),
        'Table'[LSV]
    )
RETURN
    Result

Gets the current year, and then subtracts 1. Then, the SUMX() only works on the previous year's data from the table provided by FILTER().

[sugloevg]

Thank you a lot, guys!

[edhans]

Try this @sugloevg 

Prior Year = 
VAR PriorYear = MAX('Table'[Year]) - 1
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Year] = PriorYear
        ),
        'Table'[LSV]
    )
RETURN
    Result

It gets the current year, then subtracts 1. Then the SUMX() only operates on prior year data from the table provided by FILTER().

[nandukrishnavs]

@sugloevg 

Try this DAX measure

PREVLSV =
VAR _Year =
    SELECTEDVALUE ( 'Table'[Year] )
RETURN
    CALCULATE (
        SUM ( 'Table'[LSV] ),
        FILTER ( ALL ( 'Table'[Year] ), 'Table'[Year] = _Year - 1 )
    )

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂 

[az38 · Accepted solution]

Hi @sugloevg 

Measure has no real sense with just a [Year] in filter sentence

try

PREVLSV = 
var _curYear = MAX('Table'[Year])
RETURN
CALCULATE(SUM('Table'[LSV]), FILTER(ALL('Table'), 'Table'[Year] = _curYear -1))

[Osama14]

Hi @az38 

I have used your formula but what if I want to get the month breakdown?  It's showing the total sum but I know it's because of the Calculate Sum but don't know How to get month breakdown 

[az38]

Hi @Osama14 

you can try SAMEPERIODLASTYEAR() function.

like

CALCULATE(SUM('Table'[LSV]), SAMEPERIODLASTYEAR('Table'[Date]))