Formula That Only Includes Data Within The Last 5 Years

[ValueCreate]

Hi! I hope I'm able to word this right. I'm essentially asking if theres a way to max out the formula below after 5 years. The formula below is looking back at a historical data. For example, right now its calculating for 6.30.20 by looking back at 6.30.19, 6.30.18, 6.30.17, and all the way until the data begins and taking the average (it is doing the same for the other quarters). But what if I want it to only look back 5 years? (So 6.30.19 until 6.30.15 and then for Q3 it would be 9.30.19 - 9.30.15)

Is there a measure I'm able to add so its always taking the average of the last 5 years? And some clients have less than 5 years of data, so I'd think whatever formula is used the clients with less data should be fine.

Thanks!

formulafata formula references

[v-lili6-msft]

hi @ValueCreate 

Just add a conditional in the formula as below:

&&YEAR( 'ValueCreation Ratios' [EffectiveDate])>=YEAR( 'ValueCreation ProjectedQuarters' [ProjectedQuarterEndDate] )-5

in the red arrow of this screenshot:

Regards,

Lin

[ValueCreate]

Hi,

So adding that conditional formatting did not work. As you can see in the image below all the averages changed (each Q1 should be the same number, and so on for the other quarters) and it removed the last 4 quarters (6.30.25-3.31.26).

[ValueCreate]

Thanks Lin! I will take a look at this next week and let you know if it works.

[Greg_Deckler]

Add an && YEAR([Column]) >= YEAR([Column]) - 5 ?