Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to properfly filter data for next week?

I have a full DimDate dimension with all columns, and I try to filter data for ‘next week’.

 

 

I have tried:

'Date'[Week Year Week Number] = CONVERT(CONCATENATE( YEAR(TODAY()  ),WEEKNUM(TODAY(),21)    ),INTEGER)+1    

 

But of course I have 2 problems:

  •  when week is 52, I will just get 53.
  • Also the column  Week Year Week Number   has values like 202202 And my WEEKNUM concat returns something like 20222 (they are different!)

 

How can I appropriately filter for ‘next week’ data?

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

    Try to create a column like this:

     

    Week Year Week Number = 
var _Date=[Full Date]
// change the Full Date to your Date
var _Month=MONTH(_Date)
var _yearOfTheDate=YEAR(_Date)

var _NextWeekNum=Format(weeknum(_Date+7,21),"0#")
var _int_WeekNum=CONVERT(_NextWeekNum,INTEGER)

var _preYear=CONVERT((_yearOfTheDate-1)&_NextWeekNum,INTEGER)
var _thisYear=CONVERT(_yearOfTheDate&_NextWeekNum,INTEGER)
var _nextYear=CONVERT((_yearOfTheDate+1)&_NextWeekNum,INTEGER)

var _if=IF(_Month<=1,IF(_int_WeekNum>50,_preYear,_thisYear),IF(_int_WeekNum>2,_thisYear,_nextYear))
return _if

     

     

     

    Result:

     

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

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

2 Replies