Show information until the present month

[#1]

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!

[Oleg_Troyansky]

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,

[#3]

Thanks Oleg for your answer. I don´t have a calendar table...

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

Thanks

[#4]

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...

=monthstart(addmonths(today(),1))

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.

[#5]

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

[#6 · Accepted solution]

You can add this:

WHERE [Your Date Field]<=today();

below your FROM statement.

[#7]

Based in answer of Trent.Jones, i solved this problem.

Thanks to all!!!!