Filter X-axis on last n periods, possible?

[fenixen]

Hi 

I want one of my reports to automatically show data for last n periods, for instance last 5 weeks. For instance a line graph showing values per week for 5 weeks. 

I cant find any date filtering functions in the power bi desktop that allows me to filter the X-axis like this.. 

Its a big SSAS data warehouse and i would prefer not having to filter the data in Power Query as this would ruin the other reports based on the same data. 

Any tips? 

[asocorro · Accepted solution]

You cannot filter an axis value directly on a visual; you need to create filter values outside of it.  One obvious way is to load only the recent data you want.  Another option, if you don't want to change your load logic, is to flag each record as being "recent" and then plot based on that.  I am sure there are variations on how to do it, but here's one way:

For example, I have a sales table called Data with total sales for each day per store.  It has fields Date, Store, and Actual Sales.  I created a new column called Is Recent Date, like this:

Is Recent Date = IF(DATEDIFF(Data[Date], TODAY(), DAY) <= 180, TRUE(), FALSE())

with which I flag records that are 180 days or less in the past.

Then I created a new measure, Recent Sales, that sums the Actual Sales but only for recent records, like this:

Recent Sales = CALCULATE(SUM([Actual Sales]), Data[Is Recent Date] = TRUE())

I then charted Date and Recent Sales and got the desired result. 

This works because the bar chart will exclude blank values and Recent Sales is always blank for dates that are not recent.

[fenixen]

Thank you, good suggestion ! 🙂