Calculate previous/ last week sales with a custom calendar

DAX DAX Calculations
1

Hi guys, (my first post)
I’ve watched this youtube video (from SAM) about calculating last week sales, regarding the first week of the year. Awesome.

Net Revenue LW =
VAR CurrentWeek = SELECTEDVALUE(‘Main Calendar’[WeekN])
VAR CurrentYear = SELECTEDVALUE(‘Main Calendar’[Year])
VAR MaxWeekNum = CALCULATE(MAX(‘Main Calendar’[WeekN]), ALL(‘Main Calendar’))
RETURN
SUMX(
FILTER(All(‘Main Calendar’),
IF(CurrentWeek = 1,
‘Main Calendar’[WeekN] = MaxWeekNum && ‘Main Calendar’[Year] = CurrentYear -1,
‘Main Calendar’[WeekN] = CurrentWeek - 1 && ‘Main Calendar’[Year] = CurrentYear) ),
[T Net Revenues] )

I know that i should learn more about using nested IFs or SWITCH (TRUE() but i’m not sure if this the right approach for what i need which is, calculating a measure to calculate the sales of the week -2, another for week -3 and another week -4.
For example, the measure for the sales of 2 weeks before should be something like:

…
…IF(CurrentWeek = 1,
‘Main Calendar’[WeekN] = MaxWeekNum && ‘Main Calendar’[Year] = CurrentYear -1,
‘Main Calendar’[WeekN] = CurrentWeek - 1 && ‘Main Calendar’[Year] = CurrentYear) ),
IF(CurrentWeek = 2,
‘Main Calendar’[WeekN] = MaxWeekNum && ‘Main Calendar’[Year] = CurrentYear -1,
‘Main Calendar’[WeekN] = CurrentWeek - 2 && ‘Main Calendar’[Year] = CurrentYear) ),

and the sales for 3 weeks before…

…IF(CurrentWeek = 1,
‘Main Calendar’[WeekN] = MaxWeekNum && ‘Main Calendar’[Year] = CurrentYear -1,
‘Main Calendar’[WeekN] = CurrentWeek - 1 && ‘Main Calendar’[Year] = CurrentYear) ),
IF(CurrentWeek = 2,
‘Main Calendar’[WeekN] = MaxWeekNum && ‘Main Calendar’[Year] = CurrentYear -1,
‘Main Calendar’[WeekN] = CurrentWeek - 2 && ‘Main Calendar’[Year] = CurrentYear) ),
IF(CurrentWeek = 3,
‘Main Calendar’[WeekN] = MaxWeekNum && ‘Main Calendar’[Year] = CurrentYear -1,
‘Main Calendar’[WeekN] = CurrentWeek - 3 && ‘Main Calendar’[Year] = CurrentYear) ),

This measure’s not working of course and i’ve also tried with SWITH(TRUE().
Any one has any idea how to solve this?
Thanks in advance
Best regards
Pedro

2

Pedro,

An easy way to solve this issue is to use the attached document to setup for your date table. There is a column for WeekOffset that will help you with your measure. 0 will = current week, -1 will be one week before, and so on. Try working this out once you have the date table setup. Should be pretty easily done with the CALCULATE function.

Here is a video on how to setup the Date Table if you are unfamiliar:

Power BI Date Table Code with Offsets - 4-6-2020.txt (6.8 KB)

Thanks
Jarrett

3

Hi Jarret
Thank you for your answer. I’ve just replaced my calendar table with this one (!!) I believe my table had almost all these columns…almost. Maybe it’s this “almost” that will make the difference. I don’t know how but could you help me, accordingly with these new columns, how could i get a measure for last week, another for 2 weeks ago and so on…I will analyse only up until 4 weeks ago.
Thank you very much for helping me
Best regards
Pedro

4

Hi @pedroccamara,

Give this pattern a try, this will always calculate Sales for Last Week for example.

Sales LW =
   CALCULATE( [Total Sales],
      FILTER( ALL( Dates ),
      Dates[WeekOffset] = -1 )
   )

For two weeks ago just change the -1 into -2 and you are set…
I hope this is helpful.

5

Pedro,
Here is what it would most like look like For Last Week Sales:

Last Week Sales =
CALCULATE ( [Sales], FILTER ( ‘Date’, ‘Date’[WeekOffset] = -1 ) )

** All you do is change the # at the end to get 2 weeks ago and so on. 0 will give you current week Sales**

** Just saw Melissa’s post. Either one of our Measures should work!**

Thanks
Jarrett

6

Hey Melissa,
Thanks for your quick reply.
I believe this is a very good solution for, a card for example. I didn’t thought of that. Very good tip…but. I didn’t mentioned before that i wanted for a table, i mean, when you get to see the year and week number. every line should have the sales from previous week, you know? I’m so sorry. Maybe i didn’t explain well myself…

7

Hey Jarret
Thank you very much anyway

8

There’s a quick fix for that. Just create a variable to get that WeekOffset value and amend the pattern.

Sales LW =
VAR myWeekOffset = SELECTEDVALUE( Dates[WeekOffset] ) 
RETURN

   CALCULATE( [Total Sales],
      FILTER( ALL( Dates ),
      Dates[WeekOffset] = myWeekOffset -1 )
   )
9

Hey Melissa
Still not working !

Capture
Capture363×517 34.1 KB

10

Yeah ok, can you provide a small sample PBIX, this should be easy to fix once I see what’s in play

11

What do you mean by small sample? Do i send you the sales table and the calendar table? Wouldn’t you prefer to arrange a Zoom meeting or whatever?

12

Just a mock up of your file with fictitious data.

Something like I’ve created for you… so here’s the measure in action…
Maybe you can trouble shoot with this file as comparison - if not… you know what to do :wink:

image
image339×506 12.2 KB

I hope this is helpful. Here is my sample file:
eDNA - LW Sales.pbix (448.9 KB)

13

Your solution in this file is different…let me try this one. Meanwhile…

Maybe you can see the problem here in this file. This is some data (costs and revenues) and in the same workbook the calendar.
Thank you so much for helping mePBI File.xlsx (152.2 KB)

14

Few remarks and feel free to use my sample file as a reference.

  • This is just data (xlsx) without a model (pbix) - all Measures need a well organized Model and I can’t see how that is set up based on this file…
  • The Dates table doesn’t contain a WeekOffset column like Jarret advised and provided

please examine the PBIX I provided in my last post, I hope all will become clear then.

15

Yes i’ve added a new column in dax for WeekOffSet. Now, as you suggested, i’ve change that calendar table with yours.
There’s now a mistake in that measure, in the second week of the year and sometimes in the first week. Does this ring a bell to you? I don’t know but shouldn’t be the last week of the year the 53 and the first week starts always on the first day of the year? Maybe…
Just for you to know, our week starts on mondays and also 1/1/17 can’t be week 52 and, 31/12/18 and 31/12/19 can’t be week 1

16

Well that depends doesn’t it…
The calendar provided does something that you can best compare to an iso week number - this way you don’t get partial weeks in the Dates table (weeks with less than 7 days). Understandably this can result in dates from the prior year being present in week 1 OR dates from the next year in week 52/53.

In the xlsx you provided WeekNo 1 has just one date Jan 1st 2017 which is a Sunday as far as I know - what is the WeekNo for Dec 31st 2016?

Anyway the WeekOffset M code is based on the StartOfWeek for each Date and will therefore always create an offset of 7 days.

17

Well the file that i’ve send you, was already with the “new” calendar.
The WeekNo for Dec 31st 2018 should be number 53 i guess. What i think it would work, but i’m not sure, every week should be that week number of that year. We can’t have week numbers with dates from other year you know? Like 31/12/18 and 31/12/19 being week 1 or 1/1/17 being week 52. Do you agree with me Melissa?

18

We apply the first 4 day week as week 1, so no - but hey that doesn’t matter.

However you will have to define a new WeekOffset method that is in line with your logic. Please be aware that you will end up comparing weekly values potentially containing less than 7 days with each other if you deviate from the Calendar function provided.

19

I think you’re right Melissa. I’m not gonna touch the calendar. Back to the measure then.
2nd week of 2018 is wrong and don’t know about the first cose i don’t have data of 2017. The first and second week of 2019 are wrong also but in 2020 only the second week is wrong…weird.
I’d like to help you but I don’t understand this dax measure at all. Can you? OR, can you write a SWITCH (TRUE() in my measure above, my first message?

20

Sure I can look at it later, will come back to you.