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?

  • Adil_Rafique's avatar
    Adil_Rafique
    New Member
    3 years ago

    I'm using this formula to get distinct count instead of sum as I have to count the number of emails.

     

    But I'm not getting the right output with this relation below:

     

    Count This Week = CALCULATE(DISTINCTCOUNT('SharedMailbox'[Sender.Address]), FILTER(ALL('SharedMailbox'),'SharedMailbox'[WeekRank]=max('SharedMailbox'[WeekRank])))
     
    Count Last Week = CALCULATE(DISTINCTCOUNT('SharedMailbox'[Sender.Address]), FILTER(ALL('SharedMailbox'),'SharedMailbox'[WeekRank]=max('SharedMailbox'[WeekRank])-1))
     
    P.S: I have performed the other steps for week rank and week start date and week end date.
    everything is ok, just getting the wrong count for above 2 measures.
     
    Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!