Last week formula

[nasbkrv]

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.

[V-lianl-msft · Accepted solution]

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]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Adil_Rafique]

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

[KatBous]

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