Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a Previous Week and Current Week Slicer

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"))).
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi 

       

      Thank you for replying but it didn't work.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.