6 Replies Latest reply: Mar 21, 2011 11:58 AM by ezequiel.villemur

# Show information until the present month

Hello togehter, i have the following question, i hope its understandable.

I have a Pivot Table that compares budget with real movements.

I load the complete budget from an own company´s system to the month of July of 2011

I need to show the information until the present month (March 2011)

Can you help me?

Thanks and regards!

• ###### Show information until the present month

One of the simplest solutions would be to add a calculated "flag" field in your Calendar table, something like "YTD_Flag" - for all dates until the present Month, the flag = 1 and for all other dates, Flag = 0.

Then, in your expression, you can do one of the two:

1. Multiply by the flag: sum(Budget*YTD_Flag) - only those dates with Flag=1 will be included,

2. Or, if you like Set Analysis, you can use the flag in Set Analysis: sum( {<YTDFlag = {1}>} Budget)

cheers,

• ###### Show information until the present month

I understand your propose solution, but you can suggest another solution?

Thanks

• ###### Show information until the present month

There are so many different ways to do it and I'm not sure which is the "best" one but I would just make a variable (we'll call it vMonthStart)for the first date you don't want, assuming you want March 2011 to be the last month I would use something like...

For dimension I would use...

=dual(month([Date]) & ' ' & year([Date]),Makedate(year([Date]),month([Date]),1))

Expression:

=sum({\$<[Date]={"<\$(vMonthStart)"}>} [Budget])

Off the top of my head I don't know if the syntax and everything is perfect above but that's the general idea. I'm assuming you don't want to use the script (since you didn't like the solution above this post) so you wouldn't have to with that solution but I'm sure somebody smarter than me will come in here and give you a better solution.

Hopefully that helps.

• ###### Show information until the present month

The load of the budget cannot be limited so that it does not bring the later months to the present one??

I tried in script limit the load with the function TODAY, but don´t work

• ###### Show information until the present month