9 Replies Latest reply on Sep 6, 2018 4:03 AM by Ankit Bansal

# YTD with Periodic in same Table

Hi to all,

I have been trying with no success do far to create a table showing YTD and Periodic movement for my P&L accounts at the same time with filters selecting year and month. So it would show the movement for that month (periodic) plus the accumulated movement up to the month and year selected by the filters.

My lowest level is monthly.

Any ideas ?

Tks and Regards

yannis

• ###### 1. Re: YTD with Periodic in same Table

you can calculate YTD movement as below:

{fixed year : sum(if [month] <=#month# then movement end)}

this will give you YTD and monthly you can get normally.

• ###### 2. Re: YTD with Periodic in same Table

hey Ankit thanks so much!

what is the #month#?

I get an error

• ###### 3. Re: YTD with Periodic in same Table

#month# is a parameter. I thought you are using parameter to control year and month to filter.

Nevertheless can you tell me which is the date field  in your data source based on which you need to calculate YTD?

• ###### 4. Re: YTD with Periodic in same Table

the field name is date , only care for years / months and my measure is "Actual":

• ###### 5. Re: YTD with Periodic in same Table

To achieve this you need to create parameter control only for the user to select year and month.

then you can use below formula:

{fixed year([DATE]) : sum(if month(DATE) <= #month# then movement end)}

• ###### 6. Re: YTD with Periodic in same Table

if i bring another dimension remains the same value for all depts, if I remove seems ok

{fixed YEAR([DATE]) : sum(if MONTH([DATE]) <= [Month Parameter] then [Actual] end)}

:

• ###### 7. Re: YTD with Periodic in same Table

If you need department wise you can add that in fixed calculation:

{fixed YEAR([DATE]) , [Department] : sum(if MONTH([DATE]) <= [Month Parameter] then [Actual] end)}

• ###### 8. Re: YTD with Periodic in same Table

Thanks Ankit, my YTD works fine now!

One more, how do I go about creating PY prior year YTD?

The formula below does not seem to work, gives me current year periodic:

{fixed DATEADD('year', -1, [DATE]), [Account Member], [Department] : sum(if month(DATEADD('year', -1, [DATE])) <= [Month Parameter] then [Actual] end)}

• ###### 9. Re: YTD with Periodic in same Table

Use this for PY:

{fixed  [Account Member], [Department] : sum( if year([DATE]) = [YEAR Parameter]-1 then [Actual] end)}

I hope you have year parameter separately, else you can drive from month parameter.