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 (YEAR (Dates[Dates] ) = YEAR ( TODAY () ) &&  Dates[Dates]=TODAY()-7,"Yes", "No"). Last week includes dates from 2020 (28-31 Dec) and from 2021 (1-3 Jan), so the Last week formula doesn’t work right. I tried to use multiple selection, choosing 2020&2021 from the year filter, but even then, my last week calculation didn't work.

Do you have any ideas for a Last Week formula which can interact with the Year filter correctly, especially in cases where the last week includes dates from two years.

Thank you.

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.

5 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    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

    • 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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!