Forum Discussion

jansenuy's avatar
jansenuy
Helper I
9 years ago
Solved

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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • jansenuy's avatar
    jansenuy
    9 years ago

    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.

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    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

      • dearwatson's avatar
        dearwatson
        Continued Contributor
        9 years ago

        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