Search code examples
powerbidax

How to make a measure showing last year value work with week, qtr and year columns in matrix visual?

I have column for SalesAmt im my data model.

In matrix visual I have customer on the rows, and year/qtr/week on the columns.

Year values are: 2021, 2022,..

Qtr values are: Y21 Q1, Y2 Q2,...

Week values are: W1, W2,...

I've got 2 measures that I want to display in the values section of the matrix visual:

  1. Sales = sum(sales[salesamt])

  2. Last year calc:

Sales LY =

var cy=max(calendar[yr])

VAR wk=max(calendar[wk])

return calculate(sum(sales[salesamt]),removefilters(calendar),calendar[yr]=cy-1,calendar[wk]=wk)

The above works fine for cells that are under the week, but for totals of week, qtr and yr columns I get wrong value for the LY value. I know this is to do with the fact that last week is used in the DAX. How to make this universal measure that can be applied at qtr and yr level?

Solution

  • Try the following:

    Sales LY =
  var cy = MAX(calendar[yr])
  var wks = VALUES(calendar[wk])
  RETURN
    CALCULATE(
      [Sales],
      REMOVEFILTERS(calendar),
      calendar[yr] = cy-1 && calendar[wk] IN wks
    )