It is difficult to know exactly what you want without seeing the data but have you considered adding a Parameter to the sheet for the Month number and inserting that parameter name in you formula (Note you could also use Month(today()) or a date range based on today or the parameter
Let me know if this helps
I'm a bit of confusing of what exactly you need.
My guess is if the month is the last month of a quarter, you show it as quarter value, so you can use % to get the last of a quarter dynamically. Please see below highlighted code:
IF MIN(MONTH([Month])) != MAX(MONTH([Month])) THEN
SUM(IF MONTH([Month])%3=0 THEN [Performing houses TY] END)
ELSE SUM([Performing houses TY])
Not sure if it helps you or not, let me know if any help.
i need to give an overview showing 12 month and 4 quarters. the rows need to show the values for last year and this year. in adition i need to calculate targets and variances between the years. the challenge i have is to present the quarterly figures as the last month in the display. for quarter 1 it would be month march. for quarter 2 i need to show the month of april as i dont have data for may this year yet. so unfortunately it isnt always month 3.but is the last month with data in a quarter.
if a quarter has no data for this year as quarter 3 and 4 it should show the subtotal from quarter 2 (or the last quarter with data)
i will however test the formula the way you have described it with the % and see what the impact is
thank you for looking at this !!
the formula i show is only working for the month 3 but i put that in to display which value i would like to see in the subtotals for quarter 1. so i dont want the user to select the subtotal value via a parameter.