Creating a rolling 6 week date range for use in graphs

[mur2za]

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.

[GilesWalker · Accepted solution]

@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

[arpitsa]

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 - Arpit.sawant@firstsource.com / Sawantarpit03@gmail.com

[mur2za]

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]

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

[mur2za]

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

[GilesWalker · Accepted solution]

@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 · Accepted solution]

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

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