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

Question

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:

Sales = sum(sales[salesamt])
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?

Sam Nseir · Accepted Answer · 2023-12-01 03:13:33Z

3

+50

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
    )

edited Dec 1, 2023 at 3:13

answered Nov 28, 2023 at 9:31

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

variable Over a year ago

If you see my dax, the last year value must be based on the same week number last year, and not based on dates

Sam Nseir Over a year ago

Understood and on re-reading your question - you need to capture all the values of wk. See above updated answer.

variable Over a year ago

Nice one I will try this, didn't know I could use VALUES like this

Sam Nseir Over a year ago

How did it go? If all good, please Accept the answer for the benefit of all.

Sam Nseir Over a year ago

Sorry, English saying "6 or 2 threes" = same same but different

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related