cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jansenuy
Helper I
Helper I

Display data for the past weeks

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.

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

View solution in original post

5 REPLIES 5
vanessafvg
Super User I
Super User I

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

dearwatson
Responsive Resident
Responsive Resident

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)

Capture.PNG

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

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors