Forum Discussion

djaaiin93's avatar
djaaiin93
Frequent Visitor
5 years ago
Solved

DAX for Previous Week Calculations

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!

2 Replies