Sum of last week's sales for custom 52/53 week date table in Dax

Question

I'm trying to create a measure to calculate the sum of last week's sales. My calendar is custom and held in a date table, starting in February and can be either 52 or 53 week years.

The code below is from this handy youtube tutorial (https://www.youtube.com/watch?v=jclWnA7pEvY) but I can't work out how to accomodate anything for week 1 if I'm not in a 52 week year.

In the code below, [Week_WOY] is a number from 1-53, and [Year_Uid] is the year - e.g. 2015,2016 etc.

I'm creating my tabular model in VS and then accessing using PowerBI in Live Query mode after deployment. Annoyingly that means I can't use some DAX expressions (e.g. SELECTEDVALUE).

Logically I think I want to: a) decide if the current week is week 1 b) find the number of weeks in the previous year c) return the sum of sales for that (b) week number

Here is the code I have at the moment:

LastWeekTest:=

VAR CurrentWeek = IF ( HASONEVALUE ( 'Date'[Week_WOY]), VALUES ( 'Date'[Week_WOY]) )
VAR CurrentYear = IF ( HASONEVALUE ( 'Date'[Year_Uid] ), VALUES ( 'Date'[Year_Uid] ) )
VAR MaxWeekNumber = CALCULATE(MAX('Date'[Week_WOY]), All('Date'))

RETURN
SUMX(
    FILTER(ALL('Date'),

IF(CurrentWeek = 1,
('Date'[Week_WOY] = MaxWeekNumber&& 'Date'[Year_Uid] = CurrentYear - 1),        
('Date'[Week_WOY] = CurrentWeek - 1 && 'Date'[Year_Uid] = CurrentYear)


)),
'Sales'[Sales - Inc VAT £]
)

greggyb greggyb · Accepted Answer · 2019-09-09T14:11:56

The best solution is to add a column to your date table. That column is what I would call WeekIndex. This is a value that increments by 1 every week. I don't have a setup for a 52/53 week date dim, but you can see an example of this type of column in my DimDate. With a column like this, you can write a measure like below:

Sales = SUM ( 'Sales'[Sales - Inc VAT £] )

Sales Prior Week =
VAR PriorWeek = MAX ( 'Date'[WeekIndex] ) - 1
RETURN
  CALCULATE (
    [Sales],
    ALL ( 'Date' ),
    'Date'[WeekIndex] = PriorWeek
  )

Deriving a [WeekIndex] is usually pretty straightforward if you already have a date dimension. It's usually easiest to do a distinct count of Year-Week pairs less than current week.

Else, if you want to avoid enhancing your model for some reason:

Sales Prior Week =
VAR CurrentWeek = MAX ( 'Date'[Week_WOY] )
VAR CurrentYear = MAX ( 'Date'[Year_Uid] )
VAR PriorYear = IF ( CurrentWeek = 1, CurrentYear - 1, CurrentYear )
VAR PriorWeek =
  IF (
    CurrentWeek = 1,
    CALCULATE (
      MAX ( 'Date'[Week_WOY] ),
      ALL ( 'Date' ),
      'Date'[Year_Uid] = PriorYear
    ),
    CurrentWeek - 1
  )
RETURN
  CALCULATE (
    [Sales],
    ALL ( 'Date' ),
    'Date'[Year_Uid] = PriorYear,
    'Date'[Week_WOY] = PriorWeek
  )

In general, I like to implement time intelligence using MAX to get the current value, rather than a SELECTEDVALUE or an IF ( HASONEVALUE, ...). This aligns with the way that the built in time intelligence functions are implemented.

Also, SELECTEDVALUE is in later releases of Tabular. If you're feeling pain for its lack, you should push for an upgrade.

Sum of last week's sales for custom 52/53 week date table in Dax

1 Answers