Create a Previous Week and Current Week Slicer

[TroySSAS]

Hi 

I am trying create a slicer which can show current week num as "Current Week" ,previous week num as "Previous Week" and rest normal week numbers. 

I am using below dax to create the slicer but when current week num is 1 it is not converting Week Num 52 as "Previous Week".

Weeknum Slicer=

IF('Fiscal Calendar'[Fiscal Week Number(WW)] = WEEKNUM(MAX('Fiscal Calendar'[Date])), "Current Week",IF(OR('Fiscal Calendar'[Fiscal Week Number(WW)] = WEEKNUM(MAX('Fiscal Calendar'[Date])-1) , weeknum(DATEADD('Fiscal Calendar'[MAXDATE],-7,DAY))= 52),"Previous Week",FORMAT('Fiscal Calendar'[Fiscal Week Number(WW)], "General Number"))).

[v-polly-msft · Accepted solution]

Hi @TroySSAS ,

Please have a try.

Create a column.

Flag = 
var _currentweek=WEEKNUM(DATE(2022,1,2),1)
var _countoffirstweek=COUNTX(FILTER(ALL('Fiscal Calendar'),[Fiscal Week Number(WW)]=1&&YEAR([Date])=YEAR(EARLIER('Fiscal Calendar'[Date]))),[Date])
var _week=WEEKNUM([Date],1)
var _weeknum=
IF([Fiscal Week Number(WW)]=1&&_countoffirstweek<7,
IF(DAY([Date])<=_countoffirstweek,
CALCULATE(MAX('Fiscal Calendar'[Fiscal Week Number(WW)]),FILTER(ALL('Fiscal Calendar'),[Date]=EARLIER('Fiscal Calendar'[Date])-6)),[Fiscal Week Number(WW)]),[Fiscal Week Number(WW)])
return
SWITCH(
    TRUE(),
    [Fiscal Week Number(WW)]=_currentweek&&YEAR([Date])=YEAR(TODAY()),"currentweek",
    [Fiscal Week Number(WW)]=_currentweek-1&&YEAR([Date])=YEAR(TODAY()),"previousweek",
    FORMAT(_weeknum,"#"))

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-polly-msft]

Hi @TroySSAS ,

There are some errors in the logic in your formula. I have created a simple example.

Please have a try.

Create a column.

Weeknum Slicer = 
var _cs = TODAY()-WEEKDAY(TODAY(),2)
var _ce = _cs+6
var _ps = _cs-7
var _weeknum = format(WEEKNUM([Date]),"#")
return
IF([Date]>=_cs&&[Date]<=_ce,"current week",IF([Date]>=_ps&&[Date]<_cs,"previous week",_weeknum))

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TroySSAS]

Hi 

It should be like current week 1 and previous week 53. We need only one previous week not two, is there a way to do it?

[amitchandak]

@TroySSAS , You need to have columns like

start week = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday week use for sunday WEEKDAY('Date'[Date],1)
end date= 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)//Monday week use for sunday WEEKDAY('Date'[Date],1)

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

[TroySSAS]

Hi 

Thank you for replying but it didn't work.