Filter group by week since aleatory date

[resh99]

Hi, how are you? I have a detail that I would like to know if anyone knows any way to solve.

I need to create a multiple filter that groups by week from a date determined by another filter. 

Example:

Date 1 Oct/01/2019   Week 1     Sep-30 to Oct-06

                                   Week 2     Oct-07 to Oct-13

                                   Week 3     Oct-14 to Oct-20

                                   Week 4     Oct-21 to Oct-27

Date 2 Feb-05-2020   Week 1    Feb-03 to Feb-09

                                   Week 2    Feb-10 to Feb-16

                                   Week 3    Feb-17 to Feb-23

                                   Week 4    Feb-24 to Mar-01

I use the WEEKNUM DAX, however I need the filter always shows as Week 1, Week 2, etc. no like week number by year.

Regards

[v-alq-msft · Accepted solution]

Hi, @resh99 

Based on your description, I created data to reproduce your scenario.

calculated table:
Calendar = 
CALENDAR(DATE(2019,9,1),DATE(2020,3,1))
Calendar2 = 
CALENDAR(DATE(2019,9,1),DATE(2020,3,1))


calculated column:
weekday = WEEKDAY(Calendar2[Date])

Then you may create two measures as follows.

WeekNum = 
var _datediff = MAX('Calendar'[Date])-SELECTEDVALUE(Calendar2[Date])
var _num = _datediff-TRUNC(_datediff/7)*7
var _weekday = WEEKDAY(SELECTEDVALUE(Calendar2[Date]))

return
IF(
    HASONEVALUE(Calendar2[Date]),
    SWITCH(
        TRUE(),
        _weekday = 1,
        TRUNC(_datediff/7)+1,
        _weekday = 2,
        IF(
           _datediff>=0&&_datediff<=5,
           1,
           TRUNC((_datediff-6)/7)+2
        ),
        _weekday = 3,
        IF(
           _datediff>=0&&_datediff<=4,
           1,
           TRUNC((_datediff-5)/7)+2
        ),
        _weekday = 4,
        IF(
           _datediff>=0&&_datediff<=3,
           1,
           TRUNC((_datediff-4)/7)+2
        ),
        _weekday = 5,
        IF(
           _datediff>=0&&_datediff<=2,
           1,
           TRUNC((_datediff-3)/7)+2
        ),
        _weekday = 6,
        IF(
           _datediff>=0&&_datediff<=1,
           1,
           TRUNC((_datediff-2)/7)+2
        ),
        _weekday = 7,
        IF(
           _datediff=0,
           1,
           TRUNC((_datediff-1)/7)+2
        ),
        BLANK()
    )
          
)

IsDisplay = 
var _date = SELECTEDVALUE(Calendar2[Date])
return
    IF(
        MAX('Calendar'[Date])>=_date,
        1,0
    )

Finally you may put 'IsDisplay' to the visual level filter of the result table visual and use 'Date' column from Calendar2 to slicer the result. 

Best Regards

Allan

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

[amitchandak]

Are these week of Month or they are based on selection.

For week of month please refer

https://community.powerbi.com/t5/Desktop/How-to-show-week-number-per-month/td-p/83607

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin