Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
deanbland
Helper III
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 Week0
Next Week1

 

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
selimovd
Super User
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

View solution in original post

3 REPLIES 3
goodbecenergy
Resolver I
Resolver I

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

Hey @goodbecenergy ,

 

maybe better open a new post with your issue.

 

Best regards

Denis

selimovd
Super User
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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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