So I have plotted these two measures as lines in a graph and added a date hierarchy in the X-axis (shared axis). The formula works well when drilled down to months, but when drilled to quarters, it only adds one million as shown in the formula instead of three million in the three months that make a quarter. This also does not reflect well when drilled to years too, which should add six million, the number of months passed in the financial quarter bearing the fact that the financial year starts from April. (April - Sept = 6 months gone)
If there is a way to make this Forecast Measure formula more dynamic and reflect well when drilled down to different Time frames(year, Quarter, month, weeks, days) please assist.
According to Amit's formula, if the data surpasses 12 months, it will increase by one million times the total number of months in different years. If his formula is helpful to you, then please consider Accept it as the solution to help the other members find it more quickly.Thank you.