Forum Discussion
Display data for the past weeks
Just for everyone's reference, I was able to do it by putting my weeks in the filter and then filtering it using "Top N" then setting it to a number then choosing last weeks in the setting below it. Pretty straightforward.
Hi Jansen,
A date table is new, second table full of contigeous dates that you join to your week column to give you access better time intelligence functions.
The fastest way to create a basic calendar (or "Date Dimension") table is to create a new table (Modeling->New Table) and paste in this code:
Date = CALENDAR (DATE(2000,1,1), DATE(2025,12,31))
or even better DAX can scan your data and will automatically create a list of dates with this formula:
Date = CALENDARAUTO()
These are good but take time to get the table filled out properly.
Really you should just search "Date Dimension Power BI" on the web and you will find heaps of results that you can just copy the code for both DAX and M Power Query so use whatever technique you find works best for you. Here's a link to a simple example which should work for you:
https://devinknightsql.com/2015/06/16/creating-a-date-dimension-with-power-query/
Once you have your date dimension then you should join it to the date in your sales table.
You can then use these week based time intelligence functions http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/
another option is you can just add a "Relative Date Slicer" (you need to enable it in preview features) then drag the 'Date Table'[date] onto the slicer and select relative->(Last 6 weeks)
lots of options to do this really... but I hope this sets you in the right direction
Cheers
Greg
You need a Week Number column so you will need to add columns then join the week number to you model.
Once you have a Date or Calendar table you could use the relative date slicer (Preview Feature) to show last six weeks
dearwatson Yes I understand how useful it could be to have a date dimension, but my data isn't really that date friendly. The only dates colums I have are the following:
Year: xxxx
Year/Month: xxxx/xx
Year/Wk: xxxx/xx
Now from there, I do not know how Power BI can extract date.
Also, I tried using this DAX measure as a work around:
FFE wk4 = FILTER('Master Data','Master Data'[Week]=(WEEKNUM(TODAY())-1)) which means like filtering the dataset to the week before this week
but I get this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Thanks so much.
Just for everyone's reference, I was able to do it by putting my weeks in the filter and then filtering it using "Top N" then setting it to a number then choosing last weeks in the setting below it. Pretty straightforward.