Active Week Index Column

[deanbland]

Hi, 

I am trying to create a column on my date table that shows whether a week is the current week or not like below: 

Previous Week	-1
Current Week	0
Next Week	1

I am wanting the week index number to ignore the years, as to not reset at 52. I have this working for the month, with the formula below, but can't seem to replicate with weeks... 

Month Index = VAR todaymonthstart = EOMONTH(TODAY(), 0) VAR thisEOM = EOMONTH ('Calendar'[Date], 0) RETURN DATEDIFF (todaymonthstart, thisEOM, MONTH)

Any help is appreciated! 

[Anonymous]

Hi @selimovd , your solution has worked but could you please adjust over a year break? 

[selimovd]

Hey @Anonymous ,

maybe better open a new post with your issue.

Best regards

Denis

[selimovd · Accepted solution]

Hey @deanbland ,

try this as a calculated column. You will always get the difference to the current week:

Diff to current week =
VAR vTodaysWeek = WEEKNUM( TODAY(), 21 )
VAR vRowWeek = WEEKNUM( 'Calendar'[Date], 21 )
VAR vResult = vRowWeek - vTodaysWeek
RETURN
    vResult

Probably replace the correct format of the WEEKNUM function, depending on where you live.

You can find more details here:
Calculate ISO 8601 week in DAX with hidden parameter - What the fact.bi

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi