Q:

Calculated field not working as expected

The below mentioned is the calculation field to fetch the prior months.
CASE [TimeFrame]
when 'DateMonth' then if [Months between today and Date]=0 then 'show' end
when 'Last2Months' then if [Months between today and Date]>=0 and [Months between today and Date]<=1 then 'show' end
when 'Last3Months' then if [Months between today and Date]>=0 and [Months between today and Date]<=2 then 'show' end
else 'hide'
end

The above mentioned Calculation is working as expected for Last2Months and Last3Months but its not showing any result for the current month.
Example if the user selects 11/1/2015 then,

DateMonth should be November--currently no data is shown...
Last2Months should be November and October--working as expected
Last3Months should be November,October and September --working as expected.

Also i need to add up the months as rolling sum and display automatically instead of the drop down selected.
Please find the twbx attached.
Any inputs would greatly be appreciated.

A:

You have IF statements within your CASE statement that do not have an ELSE which means you will get some NULLs in certain cases (they will not be caught by the ELSE in your CASE).
I would rewrite like this. See if this gets you your results:
IF [TimeFrame] = 'DateMonth' AND [Months between today and Date] = 0 THEN 'show'
ELSEIF [TimeFrame] = 'Last2Months' AND [Months between today and Date] >= 0 AND [Months between today and Date] <= 1 THEN 'show'
ELSEIF [TimeFrame] = 'Last3Months' AND [Months between today and Date] >= 0 AND [Months between today and Date] <= 2 THEN 'show'
ELSE 'Hide'
END

