Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating a Fiscal week Column starting for a specific date

Hi I'm trying to create a custom week and year column in PBI. My issue is that im unable to start the fiscal week from a specified date.

I would like to start the fiscal week from the Sunday, 28 February 2021 and the week to start on a sunday and finish on the 52nd week, and repeat another 52 weeks and so on until 2025. 

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Confused. You say you aren't able to start the fiscal week from a specified date and then you specify a date.

    Column =
  VAR __StartDate = DATE(2021,28,2)
  VAR __StartWeekNum = WEEKNUM(__StartDate)
  VAR __Offset = __StartWeekNUm - 1
  VAR __WeekNum = WEEKNUM('Table'[Date]) - __Offset
RETURN
  SWITCH(TRUE,
    __WeekNum <= 52, __WeekNum,
    __WeekNum > 52 && __WeekNum < 105, __WeekNum - 52,
    __WeekNum > 104 && __WeekNum < 157, __WeekNum - 104,    
    __WeekNum > 156 && __WeekNum < 209, __WeekNum - 156,
    __WeekNum > 208, __WeekNum - 208
  )