cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Active Week Index Column

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!

1 ACCEPTED SOLUTION
Super User

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

3 REPLIES 3
Resolver I

Super User

Hey @goodbecenergy ,

maybe better open a new post with your issue.

Best regards

Denis

Super User

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors