Weekday starting on friday ending on thursday

[Anonymous]

Hi all,

I will have to add a calculated column with weeks starting on friday ending on thursday.

I have a separate date table on which I would want to add this new week table to,

Who knows some dax to create this?

[LivioLanzo]

@Anonymous you calculated column can be calculated like this:

= MOD( WEEKDAY( [Date], 2 ) - 5, 7 )

[Anonymous]

Hi @LivioLanzo,

I think your formula is not right. It gave me the below shown outcomes. It should be like week 1,2,3,4,5,6,7,8,9,10 - 52 but then for weeks starting on friday ending on thursday. 

Veilweek = MOD(WEEKDAY(Datum[Date]; 2) -5; 7)

[LivioLanzo]

Ok I mustunderstood your requirement. You can do it like this, provided your date table is named Table and you have a Year column:

=
VAR PrevFriday =
    [Date]
        - MOD ( WEEKDAY ( [Date], 2 ) - 5, 7 )
VAR FirstDayOfYear =
    DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
VAR FirstFridayYEar =
    FirstDayOfYear
        - MOD ( WEEKDAY ( FirstDayOfYear, 2 ) - 5, 7 )
VAR DiffInBeginOfMonth = FirstDayOfYear - FirstFridayYEar
VAR WNum =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Date] <= PrevFriday
    )
        + DiffInBeginOfMonth
RETURN
    INT ( WNum / 7 )
        + 1

[Anonymous]

Thanks for the effort, but this formula does not provide the right weeks.

Veilweek = 
VAR PrevFriday =
[Date] - MOD(WEEKDAY([Date];2) -5;7)

VAR FirstDayOfYear =
DATE(YEAR([Date]);MONTH([Date]); 1)

VAR FirstFridayYear=
FirstDayOfYear - MOD(WEEKDAY(FirstDayOfYear;2) -5;7)

VAR DiffInBeginOfMonth = FirstDayOfYear - FirstFridayYear
VAR WNum = 
CALCULATE(
COUNTROWS(Datum);
ALLEXCEPT(Datum; Datum[Year]);
Datum[Date] <= PrevFriday
)
+ DiffInBeginOfMonth
RETURN
INT(WNum / 7 )
    +1

I believe the formula should be the exact same as the following formula in Excel:

=WEEKNUM(date; 15)

Unfortunately this formula does not work in DAX....

[LivioLanzo]

You are right. I made a small mistake in my formula in the variable:

VAR FirstDayOfYear = DATE( YEAR( [Date] ), month( [Date] ), 1 )

It should be (as the name suggests 😞

VAR FirstDayOfYear = DATE( YEAR( [Date] ), 1, 1 )

Hence the final formula is:

=
VAR PrevFriday =
    'Calendar'[Date]
        - MOD ( WEEKDAY ( 'Calendar'[Date], 2 ) - 5, 7 )
VAR FirstDayOfYear =
    DATE ( YEAR ( 'Calendar'[Date] ), 1, 1 )
VAR FirstFridayYEar =
    FirstDayOfYear
        - MOD ( WEEKDAY ( FirstDayOfYear, 2 ) - 5, 7 )
VAR DiffInBeginOfMonth = FirstDayOfYear - FirstFridayYEar
VAR WNum =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ),
        'Calendar'[Date] <= PrevFriday
    )
        + DiffInBeginOfMonth
RETURN
    INT ( WNum / 7 )
        + 1

On a side note, if you are pulling data with Power Query, this is much easier to do with Power Query with the following formula =:

= Date.WeekOfYear([Date], Day.Friday)

[TY_TY]

I love this formula, but not able to use it if my companies Year starts in October....is there any way to make this work without it going past 52 weeks? 🤣

[Anonymous]

Hi @LivioLanzo,

Unfortunately the formula provides weeks ahead of 52, and continues counting instead of starting over at 1 the next year.

So basically the way I want it is as below:

[LivioLanzo]

 thats in fact the result I am getting

[Anonymous]

Weird, i have reviewed it triple and still leading me to the wrong results...

Herebelow the formula Im using,

Veilweek = 
VAR PrevFriday =
Datum[Date] - MOD(WEEKDAY(Datum[Date];2) -5;7)

VAR FirstDayOfYear =
DATE(YEAR(Datum[Date]);1;1)

VAR FirstFridayYear=
FirstDayOfYear - MOD(WEEKDAY(FirstDayOfYear;2) -5;7)

VAR DiffInBeginOfMonth = FirstDayOfYear - FirstFridayYear
VAR WNum = 
CALCULATE(
COUNTROWS(Datum);
ALLEXCEPT(Datum; Datum[Year]);
Datum[Date] <= PrevFriday
)
+ DiffInBeginOfMonth
RETURN
INT(WNum / 7 )
    +1

[LivioLanzo]

File is here:

https://1drv.ms/u/s!AiiWkkwHZChHjwvasKJUoozrTxno

[Anonymous]

Hi @LivioLanzo,

Opened the file, copied the same exact formula and still giving me values in weeks of 80 +...

Very strange..

Would there be another way of solving this?

[LivioLanzo]

Can you share your file?

[Anonymous · Accepted solution]

So I figured the solution appeared to be an undocumented feature........ 

Veilweek (start vrijdag) = WEEKNUM(Datum[Date];15)

[Anonymous]

Super bizzare but very useful. Also works with WEEKDAY. Thanks for finding this!