0

I have the following table:

Year test is the list of previous year: 

=SUMX(Datasrc;Datasrc[Year]-1)

How do I get something similar for "Calculated field 1"? In other words, I want to get the previous "Price/kg" so it match column C, "Year test".

I have tried with the formula from Find rows relative to current row's value in excel (DAX):

=SUMX(
        FILTER(Datasrc; EARLIER([Year]) = [Year] + 1 );
        Datasrc[C_Total Asset Per Share]
)

But only get: "Calculation error in measure 'Datasrc'[Calculated field 2]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

The column A is [Year]

Cœur
  • 37,241
  • 25
  • 195
  • 267
Wizhi
  • 6,424
  • 4
  • 25
  • 47
  • Sorry, your question isn't entirely clear: you just want to see the previous year's value for a given measure? Like Sales 2015 vs. Sales 2014? – Kyle Hale Nov 04 '15 at 22:28
  • Sorry, I see there are some misstake in my text :( **Correct is:** How do I get something similar for "`Calculated field 1`"? In other words, I want to get the "`C_Total Asset Per Share`" in Column D to "jump" one row upward so B26 value will be at D25 and B25 will be at D24. see link: http://i.stack.imgur.com/DCvoI.png Thanks in advance :P – Wizhi Nov 04 '15 at 22:39

1 Answers1

3

DAX has time intelligence functions built in, so it's fairly easy to do what you'd like using the SAMEPERIODLASTYEAR function.

First you need a date table joined to your fact table. Then just do

CALCULATE ( [Base Measure], SAMEPERIODLASTYEAR(DateTable[Date]) )
Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • Thanks worked out very well. I needed to adjust my raw data to excel date, i.e. 2015-01-01 and then I could use it properly in my calculations :)! – Wizhi Nov 05 '15 at 00:09