Forum Discussion

matdub40's avatar
matdub40
Helper III
5 years ago
Solved

How to calculate the difference between two values in different years?

I have a date table that contains these columns:

 

 

I use a "Euro Gross Amount" measure in a matrix on Power BI:

 

 

I would like to calculate the difference on the Euro Gross between the different years in percentage, I used this formula but it comes out as an "enexpected tuple" error :

 

DIFF% = DIVIDE(([Euro Gross Amount] - CALCULATE ([Euro Gross Amount], SAMEPERIODLASTYEAR('date'[date])), CALCULATE ([Euro Gross Amount], SAMEPERIODLASTYEAR('date'[date]), 0)

 

Where does my error come from? Is there a better way to calculate this difference?

Need Help
  • PC2790's avatar
    PC2790
    5 years ago

    Try this:

    DIFF =
    DIVIDE (
        [Euro Gross Amount]
            - CALCULATE ( [Euro Gross Amount], SAMEPERIODLASTYEAR ( 'date'[date] ) ),
        CALCULATE ( [Euro Gross Amount], SAMEPERIODLASTYEAR ( 'date'[date] ) ),
        0
    )
     
    And convert this measure to a percentage in Measure Tools section

3 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    Try this:

    DIFF =
    DIVIDE (
        [Euro Gross Amount]
            - CALCULATE ( [Euro Gross Amount], SAMEPERIODLASTYEAR ( 'date'[date] ) ),
        CALCULATE ( [Euro Gross Amount], SAMEPERIODLASTYEAR ( 'date'[date] ) ),
        0
    )
     
    And convert this measure to a percentage in Measure Tools section
    • matdub40's avatar
      matdub40
      Helper III
      5 years ago

      Hello PC2790 ,

       

      Since this morning I have an error on my measurement "Evo%" which tells me

       

      "MdxScript(Prod_WS_RCE_Order_SKU) (145, 44) Calculation error in measure 'dwh_d_order_sku'[Evo %]: The 'SAMEPERIODLASTYEAR' function expects a contiguous selection when the date column comes from a table on side 1 of a bidirectional relationship. "

       

      Is this normal or should I change something?