topic Re: Create a Previous Week and Current Week Slicer in DAX Commands and Tips

Create a Previous Week and Current Week Slicer

Anonymous — Tue, 04 Jan 2022 14:15:20 GMT

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"))).

Re: Create a Previous Week and Current Week Slicer

amitchandak — Tue, 04 Jan 2022 14:30:34 GMT

Anonymous , 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]
)

Re: Create a Previous Week and Current Week Slicer

Anonymous — Thu, 06 Jan 2022 14:18:33 GMT

Thank you for replying but it didn't work.

Re: Create a Previous Week and Current Week Slicer

Anonymous — Fri, 07 Jan 2022 08:03:55 GMT

Hi Anonymous ,

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.

Re: Create a Previous Week and Current Week Slicer

Anonymous — Wed, 12 Jan 2022 11:20:47 GMT

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?

Re: Create a Previous Week and Current Week Slicer

Anonymous — Fri, 14 Jan 2022 04:45:35 GMT

Hi Anonymous ,

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.