Forum Discussion

mur2za's avatar
mur2za
Frequent Visitor
10 years ago
Solved

Creating a rolling 6 week date range for use in graphs

Hello all, I am a noob to Power BI, so I apologize if this has been covered to death...

 

I have a Sales table with daily sales data. I have a separate Calendar table and created a relationship to the SalesDate column in my Sales table.  

 

I need to create a graph showing sales trend for the last 6 weeks of sales. Currently, I have created a calculated column WeekEnding in the Calendar table that groups dates by Week, and used WeekEnding as my time axis, with a report level filter where I select only the last 6 weeks to show the trend. However, this is cumbersome having to go in every week and change selections, then repin the graph to the dashboard.

 

To automate the process, I created the following measures:

Now = today()

6WeeksBack = today()-42

Last6Weeks = datesbetween(Calendar[DateKey], Calendar[6WeeksBack], Calendar[Now])

 

However, a measure cannot be used as the time axis in the graph.

 

So I tried to create Last6Weeks as a calculated column instead, and I get the error "A table of multiple values was supplied where a single value was expected."

 

What am I doing wrong?? And is there an easier way to just pluck out the last 6 weeks of data to show in the graph?

 

Thanks!

 

Mur2za.

Need Help
Tips and Tricks
  • GilesWalker's avatar
    GilesWalker
    10 years ago

    mur2za instead of putting the measures you used into the graph, use them in either the page level filter or the graph visual filter. Then for your axis put in a date column. The measures should filter the axis to what you want.

     

    The other option is to create a custom column as follows:

     

    Last 42 days = IF(AND(DateKey[Date]>=[Today]-42,DateKey[Date]<=[Today]),1,0)

     

    [Today] is a measure : Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

     

    Enter the last 42 days column in the page level or visual level filter and then select 1. Enter a date column in the axis of the graph. This will maintain a rolling 42 day axis.

     

    Thanks,

     

    Giles

  • Sean's avatar
    Sean
    10 years ago

    mur2za adding a Page Level Filter will of course as the name suggests affect the remaining contents of your Page

     

    Without Filter

    With Page Level Filter

     

7 Replies

  • GilesWalker's avatar
    GilesWalker
    Icon for Skilled Sharer rankSkilled Sharer
    10 years ago

    mur2za instead of putting the measures you used into the graph, use them in either the page level filter or the graph visual filter. Then for your axis put in a date column. The measures should filter the axis to what you want.

     

    The other option is to create a custom column as follows:

     

    Last 42 days = IF(AND(DateKey[Date]>=[Today]-42,DateKey[Date]<=[Today]),1,0)

     

    [Today] is a measure : Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

     

    Enter the last 42 days column in the page level or visual level filter and then select 1. Enter a date column in the axis of the graph. This will maintain a rolling 42 day axis.

     

    Thanks,

     

    Giles

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      10 years ago

      mur2za adding a Page Level Filter will of course as the name suggests affect the remaining contents of your Page

       

      Without Filter

      With Page Level Filter

       

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    mur2za Try this in a Line and lustered Column Chart

     

    Total Sales 6WeeksBack = CALCULATE ( [Total Sales], DATESBETWEEN(SalesTable[Sales Date], [6WeeksBack], today() ) )

     

    Use the Sales Date from your SalesTable and

    Use your 6WeekBack = today()-42 measure in the above formula (a Calculated Column will not work)

    Let me know if it works.

     

  • mur2za's avatar
    mur2za
    Frequent Visitor
    10 years ago

    Awesome!!! Thank you gentlemen... both your suggestions worked great! I went with the combination of the two.

     

     

     

  • mur2za's avatar
    mur2za
    Frequent Visitor
    10 years ago

    Sorry guys, the above solution worked fine on a subset of my sales data. However, when I tried to create the Last42Days measure in my Calendar file in my master .pbix file (which contains my entire sales data from the start of 2015 through present), it gives me an error "A single value for column'WeekEnding' in table 'BasicUSCalendar' cannot be determined".

     

    This same measure worked fine in the same calendar table in the subset file - any idea why it throws this error in the master file?

    • mur2za's avatar
      mur2za
      Frequent Visitor
      10 years ago

      Never mind - I was creating it as a measure instead of a column! It's working now, thakns again for the help guys!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    i need rolling six week vs current week option in slicer and my week starts from friday to thursday 

    it should exclude the current week and take respective weeks for rolling 6 weeks

    Eg - i have data from Wk.1 to Wk.6 and wk.7 has only  4  days data and i am in 5th day so rolling 6 week would be Wk.1 (Friday) to Wk.6(Thursday) and current Week would have Wk.7 Friday till today-1

    Email - [email protected] / [email protected]