Forum Discussion

ValueCreate's avatar
ValueCreate
Icon for Helper I rankHelper I
6 years ago

Formula That Only Includes Data Within The Last 5 Years

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

 

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      ValueCreate
      Icon for Helper I rankHelper I
      6 years ago

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

       

    • ValueCreate's avatar
      ValueCreate
      Icon for Helper I rankHelper I
      6 years ago

      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).