cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User VI
Super User VI

Filter a report by year dynamically

I have a set of data that is 2014-2017, but I only want to display 2015-2017 in my Matrix. The reason I have 2014 data in the data set is the 2015-2017 calculates year over year changes, so 2014 is needed so something shows up for the 2015 YOY calc.

 

In my Matrix though, 2014 is showing and the YOY rows are blank, because 2013 doesn't exist.

 

I know i can change the filter manually, but then I have to remember to change that every Jan 1.

 

I don't want to limit my Calendar table as that will exclude 2014 records completely.

 

Any ideas how to do this? it would be great if I could drop a variable in the Report Filter section but that doesn't work. Tried it already. Only accepts constants.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Filter a report by year dynamically

think I found a solution. My Calendar table came from Power Query, so...

  • Added a custom field called "Reportable Year"
  • Gave it the formula =if Date.Year(DateTime.LocalNow()) - [Year] < 3 then true else false
  • Put the "Reportable Year" value in the report page filter
  • Checked "True"

This seems to be working with no ill effects. Kind of a hack, but not a ugly one. It shows all of 2017-2015, and the 2014 data is still being used and calculated in some of the "vs prior year" calculations in the 2015 data, so it seems to be replicating if I had hardcoded in the filter Year => 2015.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
Highlighted
Resolver II
Resolver II

Re: Filter a report by year dynamically

Can you get the Min(Year) from the fact table (this would be 2014 in your case), and then do a filter on the matrix where year <> Min(Year)?

Highlighted
Super User VI
Super User VI

Re: Filter a report by year dynamically


@olesojg wrote:

Can you get the Min(Year) from the fact table (this would be 2014 in your case), and then do a filter on the matrix where year <> Min(Year)?


I could, but I need to drag the year to the Rows section, and that is the data that needs to be filtered. Maybe I can create a measure in the Calendar table that is equal to the year unless it is Min(year) and then reports nothing and put that on the Row instead of the Year field. I suspect that will give me some weird blank though. I'll have to play with it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User VI
Super User VI

Re: Filter a report by year dynamically

Ugh. Here is my solution, and I do not like it. Seems like a hack.

 

  1. Create a new column in my Calendar table that if the year is the same as the min(year) report null, otherwise pull the year.
  2. Use the newly defined Report YEar column in the Matrix
  3. Filter the view to not show blanks in the Report Year row.

 

So, this works, and will work automatically, but it seems like an ugly hack and I'd love a more elegant solution via DAX if possible.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Microsoft
Microsoft

Re: Filter a report by year dynamically

Hi @edhans,

 

"it would be great if I could drop a variable in the Report Filter section but that doesn't work. Tried it already. Only accepts constants."

 

Do you want the visual to always display values in the last three years? If so, perhaps you could try to add a date column into visual level filter (or report level, or page level) like below:

1.PNG

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User VI
Super User VI

Re: Filter a report by year dynamically

That is a good idea, but doesn't work. It is nice to know relative date filtering is availble, but here is what happens:

  • If I pick Last 3 Calendar Years, it picks 2016, 2015, and 2014. I guess because 2017 isn't done, it doesn't accept it as a calendar year.
  • If I pick "Last 3 Years" it shows Sept 2014 - Aug 2017, so it still shows some of 2014.

There doesn't seem to be a way to have an AND there where I could pick "this year" and "Last 2 calendar years" which would do the trick.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User VI
Super User VI

Re: Filter a report by year dynamically

think I found a solution. My Calendar table came from Power Query, so...

  • Added a custom field called "Reportable Year"
  • Gave it the formula =if Date.Year(DateTime.LocalNow()) - [Year] < 3 then true else false
  • Put the "Reportable Year" value in the report page filter
  • Checked "True"

This seems to be working with no ill effects. Kind of a hack, but not a ugly one. It shows all of 2017-2015, and the 2014 data is still being used and calculated in some of the "vs prior year" calculations in the 2015 data, so it seems to be replicating if I had hardcoded in the filter Year => 2015.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Filter a report by year dynamically

Hi @edhans,

 

Thanks for your sharing. I think this solution works to filter source data rather than filtering data view. Then, would you please kindly mark your solution as an answer so that other community members having similar requirement can find the solution more easily?

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User VI
Super User VI

Re: Filter a report by year dynamically

I marked my post as a solution.

 

I cannot, as you suggest, filter the data at the source. If I filtered out the 2014 data then the 2015 report would no Year Over Year compairson data. No matter what the date range I show on the report, if there is YOY calculations, then one extra year must be there for the last report year to compare to the last report year - 1.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors