2. "Quarter" dimension in Column. This changes dynamically based the date selected.It will always display 3 quarters.
if(Date >='$(vRolling3QtrStartDate)' and Date<= '$vRolling3QtrEndDate)', Quarter)
3. Measure is an calculation = Sum(X)/Sum(total(X).
I need some help in calculating my measure, where i can display the measure as a Weighted Average of all the 3 months that falls under particular quarter. Say suppose if I select a date 11/25/2016. I will display 3 completed quarters as Q1, Q2, Q3 of 2016.
and i want the measure for Q1 and XYZ1 combination as weighted average of all the 3 months that fall under that Q1.
Any ideas or suggestions? I hope i haven't confused.
However there is a some change in the requirement itself now. Instead of displaying the weighted average of the measure for the months that are falling under that particular quarter, now i need to display the measure value only for the last month of that particular quarter. Ex: In Q1 i need to display the value of March, for Q2 it is June, Q3 it is September and Q4 it is December. Any ideas.