Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
acnt_schartner
Helper III
Helper III

Looking for real year-week

Hi,

 

i have a dax created calender 

 

1_Param_Dates =
VAR Days = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2021, 12, 31 ) )
RETURN ADDCOLUMNS (
Days,
"Year", YEAR ( [Date] ),
"MonthNumber", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"YearMonthNumber", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,
"YearMonth", FORMAT ( [Date], "mmm yy" ),
"Week", FORMAT ( [Date], "WW" ),
"YearWeek" , IF(WEEKNUM([Date])<10,FORMAT([Date],"YYYY-0WW"),FORMAT([Date],"YYYY-WW"))
)
 
but the YearWeek part of this table is wrong (last week of 2020 and first week of 2021 are the same and split). The last week of 2020 should be 53 (28.dec.2020 to 03.jan.2021 ). And 2021 should start with YearWeek= 2021-01 at 04.jan.2021.
 
Is there a simple way to get the real Year-Week value?
1 ACCEPTED SOLUTION

@acnt_schartner 

 

I have amended the year formula. Please see below

 

Year = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date]))

 

YearWeek = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date])) & "-" & WEEKNUM('Calendar'[Date], 21)

View solution in original post

4 REPLIES 4
themistoklis
Community Champion
Community Champion

@acnt_schartner 

 

Weeknumber should have this formula WEEKNUM('Calendar'[Date], 21)

and for Year you should add the following

Year = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date]))

 

The concatenation of the above 2 will give you the correct YearWeek

YearWeek = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date])) & "-" & WEEKNUM('Calendar'[Date], 21)

Thats closer but the "-1" for the year works for the dates in 2021 but not 2020

Unbenannt.PNG

@acnt_schartner 

 

I have amended the year formula. Please see below

 

Year = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date]))

 

YearWeek = IF(WEEKNUM('Calendar'[Date], 21)=53 && MONTH('Calendar'[Date])=1, YEAR('Calendar'[Date]) -1, YEAR('Calendar'[Date])) & "-" & WEEKNUM('Calendar'[Date], 21)

Thats it. Thank you.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.