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
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.
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
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…
Hey Jarret
Thank you very much anyway
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 )
)
Yeah ok, can you provide a small sample PBIX, this should be easy to fix once I see what’s in play
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?
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 
I hope this is helpful. Here is my sample file:
eDNA - LW Sales.pbix (448.9 KB)
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)
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.
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
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.
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?
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.
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?
Sure I can look at it later, will come back to you.
Hi @pedroccamara,
I’ve added a WeeknYear column to the Dates table. Logic for that column is:
[Year] * 10000 + [Week Number] * 100, type number
So this will work with Custom calendars and your Weekno requirement. Last week sales then becomes:
Sales LW (WeeknYear) =
VAR myWeekOffset = SELECTEDVALUE( Dates[WeeknYear] )
VAR PrevWeek = CALCULATE( MAX( Dates[WeeknYear] ), FILTER( ALL( Dates ), Dates[WeeknYear] < myWeekOffset ))
VAR ListWeeks = SELECTCOLUMNS( ADDCOLUMNS( VALUES( Dates[WeeknYear]), "LW", CALCULATE( MAX( Dates[WeeknYear] ), FILTER( ALL( Dates ), Dates[WeeknYear] < EARLIER( Dates[WeeknYear])))), "Weeks", [LW] )
RETURN
COALESCE(
CALCULATE( [Total Sales],
FILTER( ALL( Dates ),
Dates[WeeknYear] = PrevWeek )
),
CALCULATE( [Total Sales],
FILTER( ALL( Dates ),
Dates[WeeknYear] IN ListWeeks
)
)
)
With this result.
Here’s my updated test file. eDNA - LW Sales.pbix (451.0 KB)
I hope this is helpful.
2 Likes
Awesome Melissa, thank you so much!!!