How to select 2nd Sunday from a filter (not the newest Sunday, but the one that was before)

[Anonymous]

Hello, 

I have such a situation. I need to set up a filter where it can automatically grab not the newest date but the second newest Sunday.

  Like for this visualization I want to have forecast of the week starting 1/02, not 1/09 so i need to set up the filter so that it selects 2nd Sunday. 

I understand it should be done from here, but if I just select manually '1/2/2022' I will need to go back and forth every week and select the new date. 

Does someone know how I can do this?

[Anonymous]

@amitchandak 

I am not sure I understand what I need to do. I basically need to have a column which will look at STARTDATE column and transfer that day to previous week Sunday in a new column. I am not sure maybe it should be Calculated column or a measure... 

I read this link, but I am not sure what I need to replicate since it doesn't look like what I need. 

Week Is Not So Weak: WTD, Last WTD, and This Week ... - Microsoft Power BI Community

My apology, maybe you are right, but I really don't understand what I need to do. If you could write what I need to do, I will greatly appreciate.

[Anonymous]

I don't have a date table. Can I paste this code to my regular table?

[Anonymous]

Amitchandak thank you. I am not sure I understand what I need to do. 

 I need 

second Saturday from today

.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you need to create a disconnected calendar table as a slicer, and then create a measure and apply it to the visual level filter.

Measure = 
var _start = MIN('Table 2'[date])-WEEKDAY(MIN('Table 2'[date]),1)-6
return IF(MAX('Table'[date])>=_start,1)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,what you are asking here is WOW comparision

if yes, then check if this can help

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Amitchandak your formula gives me 1/21/2022. 

I need 1/2/2022. And it should stay like this Sunday - Saturday, and from next Sunday jump to 1/9/2022.

[amitchandak]

@Anonymous , Second Sunday based on year start or to date

new columns in date table

based on today

Second Sunday= today() + 14 -1*WEEKDAY(today(),2)

based on year start

Second Sunday=

var _today  = date(year(today()) ,1,1)

return

_today  + 14 -1*WEEKDAY(_today  ,2)

You need a column in date table

if([Date]= [Second Sunday], "Second Sunday" , [Date] )

Select and save this in slicer or filter