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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.