cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataSundowner
Helper I
Helper I

Get Same Week Ending Prior Year Using DAX

Hello everyone. I'm trying to get same week ending (Sunday) of prior year for each week. I get it to work using the following, but it only works for date goes back to 1/9/2022. If I bring in the weekending date of 1/2/2022, the formula that looks for same week prior year will fail. It gives an error saying "a table of multiple values was supplied where a single value was expected". 1/2/2022 is technically the 52th week of 2021 by ISO standard, so I would like the formula to return the Sunday of 52th week of 2020 which is 12/27/2020. I have the date dimension table that maps each date to ISO year and ISO week number. How can I fix this? Thank you in advance! 

DataSundowner_0-1670007363098.png

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

The best way to fix this is not to attempt to compute it.  Use a static calendar table that has all the required columns predefined.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

The best way to fix this is not to attempt to compute it.  Use a static calendar table that has all the required columns predefined.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.