Forum Discussion
Filter a report by year dynamically
I 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.
Anonymous 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.
Ugh. Here is my solution, and I do not like it. Seems like a hack.
- 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.
- Use the newly defined Report YEar column in the Matrix
- 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.
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:
Regards,
Yuliana GuThat 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.
I 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.
