DAX for Previous Week Calculations

[djaaiin93]

Hi,

I am using RADACAD's date script for my data model.
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

I’m not sure if I’m doing something wrong or if others have had this problem as well.

While doing some weekly calculations- Specifically comparing current week to previous week, I found an issue where the last week of the year 2020 (week 53) and the first week of the year 2020 (week 1) have the same start date 28 Dec 2020. Also, the week starting on Jan 4, 2021 is week 2 rather than week 1. And so my DAX measure isn’t working for those weeks. Could you please help with this?

Here’s what it looks like

Here’s my measure

TotalSales PW =
VAR CurrentYear =
IF ( HASONEVALUE ( Dim_Dates[Year] ), VALUES ( Dim_Dates[Year] ) )
VAR CurrentWeek =
IF (
HASONEVALUE ( Dim_Dates[Week of Year] ),
VALUES ( Dim_Dates[Week of Year] )
)
VAR MaxWeekNum =
CALCULATE (
MAX ( Dim_Dates[Week of Year] ),
ALL ( Dim_Dates ) //Dim_Dates[Year] = CurrentYear – 1
)
VAR TSPW =

IF (
CurrentWeek = 1,
CALCULATE (
[TotalSales],
// DATESBETWEEN ( Dim_Dates[Date], DATE ( 2020, 12, 21 ), DATE ( 2020, 12, 28 ) )
Dim_Dates[Week of Year] = MaxWeekNum , Dim_Dates[Year] = CurrentYear – 1
),
CALCULATE (
[TotalSales],
ALL ( Dim_Dates ),
Dim_Dates[Week of Year] = CurrentWeek – 1,
Dim_Dates[Year] = CurrentYear
)
)
)
RETURN
TSPW
Here's the output

I tried adding an IF statement below. It fixes a problem for Week 2 but not week 1/week 53. it isn’t very dynamic
IF (
CurrentWeek = 2,
CALCULATE (
[TotalBundles],
DATESBETWEEN ( Dim_Dates[Date], DATE ( 2020, 12, 28 ), DATE ( 2021, 01, 04 ) )
)

I would really appreciate any help. Thanks!

[amitchandak · Accepted solution]

@djaaiin93 , You can Either create ISO weeks , which basically only start after the year start

Weeknum([Date],21)

Or you can create new columns like , these in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

The you can have measures like , Last year same week is 52 week behind

example

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Also same weekday laste year is 364 days behind

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY)

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[djaaiin93]

All Hail @amitchandak ! 

Thanks a million 🙂