Forum Discussion

RAdams's avatar
RAdams
Helper III
5 years ago

Select Current Week, Previous Week and Previous Year Week

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!

 

 

 

Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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-Last-Week/ba-p/1051123

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.