Forum Discussion

n3ptune's avatar
n3ptune
Helper II
5 years ago

Week relative filter or week offset

Hello all

I want to have a relative week filter or just offset filter .

I have year to date revenue and last year to date. once i choose last 1 week in date relative filter the visual shows only this year.

So i think i have to play with offset weeks for instance -1 week that will show for both years last 1 week amounts.

Any way to calculate the offset number of week which will start from 1 when each year changes (fin year 1/4 - 31/3)

 

with 
datediff (today() , calendar(date),week) it return offset but number is getting bigger and bigger once the year changes and it not stops -52 or 52.

anyone can help?

Need Help

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    n3ptune Weeks are a pain to deal with in Power BI compared to normal dates, as you've found. What I do is instead of dealing with week offsets is just look at week numbers. If your date table is in Power Query, you can add a week number to the date using the following function when adding a column to your Date table.

    Date.WeekOfYear([Date])

    This only breaks down at the end of the year when you occasionally get one year with 52 weeks and another with 53. There is no "53" to compare to in the other year, but this is the issue with weeks.

    I also have a "IsCurrentWeek" column that is true/false in the data table.

    Date.IsInCurrentWeek([Date])

    That will let me grab the week number in DAX, then filter all other years on that same week number. The default I believe is Sunday for the start, but you can add optional start days using Day.Monday, Day.Saturday, etc. For example:

    Date.WeekOfYear([Date], Day.Saturday)

    You can see my more complete date table here which also links back to a blog I wrote to make it fully dynamic.

     

    Additionally, SQLBI has a great set of articles and videos for DAX patterns for weeks here.