Get Last year Amount

[bmed]

Hi All,

I am creating a report and the DS is SQL Server table. It is a simple table with 3 columns YEAR,REGION,AMOUNT.

I have a report with a slicer as YEAR and a score card showing the amount (filter based on the visual tied to REGION). My ask is I want another score card next to the one I have and should dispaly PREVIOUS YEAR AMOUNT. For example if I select the slicer to have 2019, my first score card should show Amount for 2019, the new one should show for 2018, is that possible? I tried using "SAMEPERIODLASTYEAR" , but that needs a DATE, but my table is aggregated by YEAR. Please advise.

Thanks

[mhossain]

@bmed 

Try to create a measure like below:

PreviousYearAmount =

CALCULATE(

SUM('Table'[AMOUNT]),

ALL('Table'[YEAR]),

'Table'[YEAR]=VALUES('Table'[YEAR])-1)

And drag this measure to your second card

[bmed]

@mhossain Thanks,

I am getting a syntax error "Parameter is not the correct Type"

[mhossain]

@bmed 

Seems you missed paranthesis, see below

VALUES('Billing (2)'[Service Year])-1

[bmed]

@mhossain 

Now when i drag the New Measure into the score card I get the following message

Error Message:

MdxScript(Model) (8, 1) Calculation error in measure 'Billing (2)'[PreviousYearAmount]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

[mhossain]

@bmed 

Convert this column "[PreviousYearAmount]" to whole number in powerquery probably, error shows that this is not a number column.