Showing the last 8 week from the calendar table

DAX DAX Calculations
1

*** READ FIRST ***
I have an extended calendar table ( from DNA enterprise ) start 01/01/2021 till 31//12/2025
my factables have recent data till may 2022 and ongoing. How can I create a filter from the calendar
table only showing the last 8 weeks ( format YYWW example 2201 for week 1)
as an intermediate solution I create a filter using topn(8; datecolumn ) the datecolumn being a column from the fact table.
thanks in advance
Roger

2

Hi @Roger, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hi @Roger,

A solution to do this in Power Query is:

  1. get the current date
  2. create a column to calculate the week number (this can be tricky, because different countries use different week calculations)
  3. create a column and fill it with the current week number
  4. add a column to calculate the difference between the week number and the current week number

This will result in a column in your calendar table whereby the current date in your calendar will show a “0”. The previous week will show “-1”. Then in your report you can put a filter based on this column and then selecting the number of weeks you want to show. In your case, it will be 0 up to and including -7.

4

Hi @Roger, did the response provided by the @Siantar help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hi @Roger, we’ve noticed that no response has been received from you since May 17th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.