CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9724

How to write a measure that works with any date dimension to show last year value?

I want to create a measure that can be added next to any date dimension. For example: next to year, months, week or date.

And accordingly it must show the value last year.

So if it is added next to year, then it must display value last year.

If next to year week, then value same week last year.

If next to year week day, then value same day last year.

The measure I have created is:

SALES_LY =

VAR last_YR = MAX(CALENDARTBL[YR])-1

CALCULATE(SUM[SALES], REMOVEFILTERS(CALENDERTBL), CALENDARTBL[YR]=last_YR)

But if I place this next to year then it works as expected - that is - shows last year sales value.

But if I place next to week or date then it still shows last year value.

I'm trying:

SALES_LY =

VAR last_YR = MAX(CALENDARTBL[YR])-1

VAR startdt = 
CALCULATE(MIN(CALENDARTBL[YR]), REMOVEFILTERS(CALENDARTBL), CALENDARTBL[YR]= last_YR

VAR enddt = 
CALCULATE(MAX(CALENDARTBL[YR]), REMOVEFILTERS(CALENDARTBL), CALENDARTBL[YR]= last_YR

CALCULATE(SUM[SALES], REMOVEFILTERS(CALENDERTBL), CALENDARTBL[Date]>=startdt,
CALENDARTBL[Date]<=enddt )

Is there a simpler way?

Maybe as follows:

Sales LY =   

var cy = MAX(calendar[yr])   

var days = VALUES(calendar[DayNumber])   

RETURN CALCULATE([Sales], REMOVEFILTERS(calendar), calendar[yr] = cy-1 && calendar[DayNumber] IN days     )

Upvotes: 1

Views: 110

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

SALES_LY = CALCULATE(SUM[SALES], SAMEPERIODLASTYEAR(CALENDARTBL[Date]) )

See more info on Time intelligence functions

Alternative solution without Time intelligence:

SALES_LY =
  var cMin = MIN(CALENDARTBL[Date])
  var cMax = MAX(CALENDARTBL[Date])
  var lyMin = DATE( YEAR(cMin) - 1, MONTH(cMin), DAY(cMin) )
  var lyMax = DATE( YEAR(cMax) - 1, MONTH(cMax), DAY(cMax) )
  return
    CALCULATE(
      SUM[SALES],
      ALL(CALENDARTBL),
      lyMin <= CALENDARTBL[Date] && CALENDARTBL[Date] <= lyMax
    )

Another one worth trying with your setup:

SALES_LY =
  CALCULATE(
    SUM[SALES],
    DATEADD(CALENDARTBL[Date], -1, YEAR) 
  )

Or:

SALES_LY =
  CALCULATE(
    SUM[SALES],
    PREVIOUSYEAR(CALENDARTBL[Date]) 
  )

Upvotes: 4

Related Questions