Hi Community,
We are trying to build a new measure which is historical to date (HTD) on our model and we don't know how to proceed.
Do you know if recursive formulas like this one can works or do you have another suggestion :
IIF([%TIME%].CURRENTMEMBER.PROPERTIES("YEAR"),([%TIME%].[YEAR-1],[MEASURES].[HTD])+([%TIME%].CURRENTMEMBER,[MEASURES].[YTD])
Great thanks for your help.
Kind regards
Stéphane
In general the formula like:
SUM([%TIME%].[2006.01]:[%TIME%].CURRENTMEMBER,[MEASURES].[/CPMB/SDATA])
will work correctly for base members. Please replace 2006.01 with the first member of your TIME dimension.
For parents you have to experiment with IIF...
Thanks very much for your answer
Hi Vadim,
Thanks for your answer, to give an example we want if we select time 2016.12 to obtain the amount of all month before this date (including 2016.12).
I don't know if it is clear for you ?
It is used by my company to see depreciations.
Best regards
Stéphane
Sorry, but still not clear! What do you mean by "all months before"?
Sorry, to give an exemple we have for deprec for Lands:
50 for HTD 2014, 10 for Q1.2015, 10 for Q3.2015 and in HTD 2015.12 we want to see the total amount before 2015.12 since all historical data we have.
Best regards
Stéphane
Still not clear!
May be by all months you mean all periods from the company start???
Please look on my answer here:
Hi Vadim,
That's that, the HTD measure we want is for all periods from the company start, I see your response, I think that can corresond to our case.
Thanks for your answer I am going to study the case.
Kind regards
Stéphane
Can you explain the desired calculation logic using some example instead of incorrect formula?