How to restrict to only five years of data dynamically in Power BI

[POSPOS]

Hi All,

I have a requirement to restrict my report to only 5 years of data which has end date. 

Please find sample data below : 

My requirement is to only show 2019,2020,2021,2022,2023 as 2024 does not have end data. 
I should always show 5 years of data dynamically.
Sample file is attached here.

Can someone please suggest on how to achieve this?
Thank you.

[Ritaf1983]

Hi @POSPOS 
If I understood you correctly you want to filter data before loading to the model. 
You can apply these steps with PQ : 
1. Filter out the rows without end date :

2. Add a column that calculates the dynamical duration from today to the column on which you want to base the filtering of 5 years (start/end)

3 . convert the result to years :

Filter  the needed years :

5. Delete the column that you don't need from here ( the steps are recorded and will repeat every refresh)

The pbix is attached you can follow the steps

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[POSPOS]

@Ritaf1983  - I would like to do this using DAX as we cannot filter out the data before loading.

[Ritaf1983]

Hi @POSPOS 
Then this is even easier if it is for the visual you can use filters :

if you want a calculated column to filter all page then you can use the DAX :

Filter condition for last 5 years = IF([End Date]<>BLANK() && YEAR(TODAY())>='Table'[Start Date].[Year]-5,1,0)

And use this column as a filter :

The updated PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[POSPOS]

@Ritaf1983  - We cannot apply relative date filter as the relative dates are calculated based on the current date that is from current date and back.
We may miss out on few months for the year 2019. I will need to show full data for each year.

[Ritaf1983]

Hi @POSPOS 

Look at the second part of my answer. 

Calculated column....for filter

[POSPOS]

@Ritaf1983  - 
Thanks, I tested and the results are not showing as expected.
I tested by changing 5 years to 4 years and it is still showing 1's for all the years.
Calculated column is showing as 1 even for 2019

[Ritaf1983 · Accepted solution]

@POSPOS 
Sorry, that I wrote the formula in the wrong order. 
The correct is : 

Filter condition for last 5 years = IF([End Date]<>BLANK() && YEAR(TODAY())-5<=YEAR('Table'[Start Date]),1,0)

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[POSPOS]

@Ritaf1983  - Thank you, This solution worked.