Forum Discussion

nasbkrv's avatar
nasbkrv
Regular Visitor
5 years ago
Solved

Last week formula

Hi, I have a report page with slicers for Year, Week from-To, Last week. But I have an issue with my Last Week formula and the interaction with the Year filter. Last week formula is:  IF (Y...
Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi nasbkrv ,

     

    Please create Year_Week column as filter condition first.

     

    Year_week = 'Table'[week]+'Table'[year]*100

     

    Then create Last week column:

     

    IsLastWeek2 = 
var current_date = CALCULATE(
    MIN('Table'[Date]),
    FILTER('Table',
    'Table'[Year_week]=MAX('Table'[Year_week]))
    )
return 
IF('Table'[Date]<current_date&&
    'Table'[Date]>=current_date-7,
"Yes",
"No")

     

     

    Sample .pbix

     

    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's avatar
amitchandak
Super User
5 years ago

nasbkrv , Better to have a separate date or week table. Then have rank on Week start date or Year Week YYYYWW format and use that

 

New column

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

 

new measures
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))

 

refer 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

KatBous's avatar
KatBous
Frequent Visitor
3 years ago

amitchandak any idea why This week and last week return blank?