3 Replies Latest reply on Jan 27, 2016 12:51 PM by Norbert Maijoor

# Calculation Field -  how to include one measure with values of different time periods? (Current Period minus previous Period)

Hi All,

I am trying to build some ratio calculations which require to use values in the both current and previous field.  For example, calculate year over year cash collected,

Formula is : "cash collected = Revenue current + AR previous month – AR current "    ( Two measures: Revenue and A/R, also Date value)

I am not sure whether to use " If or case when" statement, but it would be difficult to select the condition as "previous month"

Also, I have no idea how should I deal with the "Date" value, it is in Date format as well, and I am able to customize it as "Month"

Does anyone have experience with this kind of calculation?   Any help is very much appreciated!

Thank you ~

• ###### 1. Re: Calculation Field -  how to include one measure with values of different time periods? (Current Period minus previous Period)

Flora,

Find my approach below and stored in attached workbook version 9.0

a. AR previous Month: LOOKUP(sum([AR]),-1)

b. Cash collected: sum([Revenue])+[AR previous Month]-sum([AR])

• ###### 2. Re: Calculation Field -  how to include one measure with values of different time periods? (Current Period minus previous Period)

Hi Norbert,

That works perfect in my case.  Thank you very much for the help and the sample workbook.  I really appreciate your help! =)

• ###### 3. Re: Calculation Field -  how to include one measure with values of different time periods? (Current Period minus previous Period)

Hi Flora,

Glad I could help out. The "awards" are appreciated!:)