Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 , 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |