Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Get Last year Amount

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

Need Help

5 Replies

  • mhossain's avatar
    mhossain
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Anonymous 

     

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      mhossain Thanks,

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

      • mhossain's avatar
        mhossain
        Icon for Solution Sage rankSolution Sage
        4 years ago

        Anonymous 

         

        Seems you missed paranthesis, see below

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