Actual and Budget based on Month-Year selection

[vemula]

Hello All,

Please help me

I have data like below

Month   Actual   Budget   variance
Jan           12,555   12,343   212
Feb                      23,456    (23,456)
Mar                      65,678   
Apr                      12,345   
May                      56,876   
Jun                      23,456   
Jul                      34,565   
Aug                      23,434   
Sep                      12,897   
Oct                      22,365   
Nov                      12,345   
Dec                      21,321   


i have filter for Month - Year

Requirement 1: when user user select Jan 2017, Actual data should show only for Jan, but budget data should show for entire year, variance should show only for Jan
If user select Feb 2017, if actual data is available it should show, budget should show entire year, variance should show Jan and Feb

Requirement 2: at the bottom we need to have a total row, if user select Jan then Actual will be for Jan(12,555) and Budget is (12,343)
                          if user select Feb then Actual will be Jan+Feb and budget should be also Jan+Feb


your help is appreciated

Thanks
vemula

[bdbits]

You have not said if this is relational or dimensional.

One way would be to create some data items with IF expressions that compare the month for an amount with the month selected, something like

if([AmountMonth] <= Month) then ([Amount]) else (null)   <== not a real expression, just the general idea

If you have months which do not have amounts, you may also need an outer join the the report queries.