CODEWITHSUNDEEP
excelrowpowerpivotdax
Wizhi
Wizhi

Reputation: 6549

Previous Year row in measurement, DAX formula

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]

Upvotes: 0

Views: 849

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

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]) )

Upvotes: 3

Related Questions