Select Current Week, Previous Week and Previous Year Week

[RAdams]

So here's my dilema, I need to create a report that will ALWAYS look at Selected Current Week, Selected Previous Week and Selected Previous Year Current Week Sales. 

I have a Dim Calendar which is tied to my Sales Data but I would like to be able to select any Weekend in a slicer and filter to the selected Weekend and the respective Weekends listed above. 

Here's my setup:

When I single Select a Weekend Date in the slicer, I have Weekend Selection (code below 2nd screenshot) pulling for what would be the Selected Current Week that I want to look at. I want to be able to pick any one weekend at a time.

 

Weekend Selection = 
IF(
    HASONEVALUE( 'Calendar'[Week Rank Desc]), 
    VALUES( 'Calendar'[Week Rank Desc]), BLANK())

What I would like to be able to do is be able to pick any weekend and always show that weekend, and the previous weekend and the 53rd week prior (which is prior year weekend). So if I chose Weekend 2, I want to show Weekend 2, 3 and 56. etc

I've been racking my brain on this. I tried a SummarizeColumn Table and a few other Hail Mary's but nothing seems to be working. 

Any help would be appreciated. 

Thanks!

[v-yiruan-msft]

Hi @RAdams ,

You can refer the content in the following links to get the sales for current week, last week and same week in previous year. If these links are not suitable for your scenario, please provide some sample data in your data model and expected result with examples. Thank you.

Calculate Previous Weeks Sales

Same Period Last Year to Date DAX Calculation in Power BI

Measures – Year Over Year Percent Change

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[amitchandak]

@RAdams , There two part Intialize LOV and This week vs last week etc

You need following column in Date table to select this week (Intialize)

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

For Time intelligence , new columns

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)

measure
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)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1

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...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.