Forum Discussion

edhans's avatar
edhans
Community Champion
9 years ago
Solved

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 n...
Need Help
  • edhans's avatar
    edhans
    9 years ago

    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.

     

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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's avatar
edhans
Community Champion
9 years ago

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.