Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
fenixen
Advocate II
Advocate II

Filter X-axis on last n periods, possible?

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? 

1 ACCEPTED SOLUTION
asocorro
Skilled Sharer
Skilled Sharer

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. 

 

recent.jpg

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

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

2 REPLIES 2
asocorro
Skilled Sharer
Skilled Sharer

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. 

 

recent.jpg

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

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

Thank you, good suggestion ! 🙂 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.