Compare values from specific dates

[Rafael Brabec]

Hello

I want to compare values from two specific dates (let's say 31st of March 2018 and 31st of December 2017, so exactly 3 months or a quarter).

 

The idea is to compare how the values changed between then and now. The same I would need for a comparison of 6 months (e.g. 31st of March 2018 and 30.09.2017)

My approach would be to calculate the value (sum of CTC) for each project for the day which is 3 months back (6 months back, respectively)

 

The 1 months comparison I can do with "Previous" but I want not to compare 31st of March with 28th of February but with 31st of December. I want to write the formula as "dynamic" as possible, meaning that in April the 3 month comparison must be with the values of the 31st of January etc.

Formula for 1 month comparison: Sum([CTC]) over (intersect([Project_ID],Previous([Date])))

 

 

I thought of manipulatingthe formula by doing something like this:Sum([CTC]) over (intersect([PSP-Element],Previous([Date], -3))), to get the 3 months comparison but it does not work.

 

Can you help me in resolving this

[Khushboo Rabadia]

You use previous number without minus like:

Sum([CTC]) over (intersect([PSP-Element],Previous([Date], 3)))