Forum Discussion

deanbland's avatar
deanbland
Helper III
5 years ago
Solved

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! 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    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

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • selimovd's avatar
      selimovd
      Most Valuable Professional
      2 years ago

      Hey Anonymous ,

       

      maybe better open a new post with your issue.

       

      Best regards

      Denis