Forum Discussion
Community ChampionFilter 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.
Community ChampionThat 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.
Community ChampionI 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.
v-yulgu-msftMicrosoft Employee
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- edhans
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.