how to get a week comparative week number column?

[Anonymous]

Hi, as there is no week number in date hierarchy, and I don't know how to create a comparative week number according a date column,

the first day of a week is Monday, and all the days' week num  equils week num of TODAY  is always marked as W,

for example, today is 2022-12-29, so the value of  2022-12-26 to 2022-12-30 should be W, and 2022-12-19 to 2022-12-25 should be W-1

and if today is 2022-12-20, then 2022-12-19 to 2022-12-25 should be W, 2022-12-26 to 2022-12-30 should be W+1,

someone propose a possible solution that create a column:

week_column=

VAR _w = WEEKNUM(TODAY(), 2)

VAR _w1 = WEEKNUM([Date], 2)；

VAR _gap =_w1 - _w

RETURN

SWITCH(TRUE(),
_w =_w1 ,"W",
_gap>0, "W+"& _gap,
_gap<0, "W-"&ABS(_gap))

one flaw is that it fails to consider the different year conditon, for example, 20230103 should be "W", and this column is W either when date is 20220101, 20210101...

do you have any ideas to solve it?

[Anonymous · Accepted solution]

HI @Anonymous,

In fact, the weeknum function result will be reset if the date range across multiple years.

So your formula will get wrong result when calculate between different years. For this scenario, you can try to use the following calculate column expression and I add condition to check and handle both same year and different year calculations:

weekFlag =
VAR tdWeekNum =
    WEEKNUM ( TODAY (), 2 )
VAR currWeekNum =
    WEEKNUM ( [Date], 2 )
VAR _gap = currWeekNum - tdWeekNum
VAR symool =
    IF ( TODAY () > [Date], -1, 1 )
VAR _stDate =
    MIN ( TODAY (), [Date] )
VAR _edDate =
    MAX ( TODAY (), [Date] )
VAR diff =
    // 'YearEnd of startDate' week number - startDate weeknumber + 'period weeknumber' + endDate weeknumber
    WEEKNUM (
        DATE ( YEAR ( _stDate ), 12, 31 )
    )
        - WEEKNUM ( _stDate )
        + SUMX (
            FILTER (
                ADDCOLUMNS ( CALENDAR ( _stDate, _edDate ), "WNum", WEEKNUM ( [Date] ) ),
                FORMAT ( [Date], "mm/dd" ) = "12/31"
                    && YEAR ( [Date] ) <> YEAR ( _stDate )
                    && YEAR ( [Date] ) <> YEAR ( _edDate )
            ),
            [WNum]
        )
        + WEEKNUM ( _edDate )
RETURN
    IF (
        YEAR ( TODAY () ) = YEAR ( [Date] ),
        //same year
        "W"
            & IF ( _gap <> 0, IF ( _gap > 0, "+", IF ( _gap < 0, "-", "" ) ) & ABS ( _gap ) ),
        //different year            
        "W"
            & IF ( symool > 0, "+", "-" ) & diff
    )

Regards,

Xiaoxin Sheng

[Ahmedx]

see if this video can help you

https://www.youtube.com/watch?v=CnkUqUzdgQU&t=275s

[amitchandak]

@Anonymous , if you compare weekday with last week same weekday

With help from date table you can use measure

7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAy))

Last year same week day

364 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAy))

for wow and wtd you need Week rank in date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])

And then measures like

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8