Display data for the past weeks

[jansenuy]

Hi so I have a problem automating my graphs/visuals to display data for the 6 six weeks automatically or even a measure (sales) of the latest week. I tried making DAX or custom column, but I had no success.

Measure/Column = if([Week Num]=(WEEKNUM(TODAY())-1),[Week Num]=(WEEKNUM(TODAY())-2),[Week Num]=(WEEKNUM(TODAY())-3),[Week Num]=(WEEKNUM(TODAY())-4),[Week Num]=(WEEKNUM(TODAY())-5)[Week Num]=(WEEKNUM(TODAY())-6), "yes","no")

This is the only way I can think of, but its not working. The problem is I do not have a good date column and I only have a week number column in the dataset formatted as text, because some errors are coming up when I transform to numbers. Anyway, I hope that this will still be possible.

Also, when using DAX, I dont know why my column "week" with the text formatted numbers doesn't show up for referencing. Whenever I try to search 'Master Data', I can't find 'Master Data'[Week]. It's not available I think. How to solve this complex situation? Thanks in advance.

[vanessafvg]

@jansenuy if you using a measure you will need to place an aggregate function before the week number i.e max(week num), if its a column then i dont think you should have the issue.

ideally if you have a date table that would really simplify your situation

[jansenuy]

Could you elaborate please? I am a total noob on this @vanessafvg

[dearwatson]

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

[jansenuy]

@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.

[jansenuy · Accepted solution]

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.