I need to create a calculated measure on a cube measuring costs in the financial YTD.
The dimension available to me has only period values available for the Calendar Period and Financial Period (i.e. 200101, 200102, etc).
Example Data in Dimension with Financial Year July - June
Example Data in Dimension with Financial Year Jan - Dec
As seen in the image, the financial period does not necessarily match the calendar period.
What would be the best approach to calculate the period range for the Financial YTD dynamically?
If today's date is 2016/03/01 and fin year is Jul - Jun then the range should be 201601:201609
If today's date is 2016/03/01 and fin year is Jan - Dec then the range should be 201601:201603
I have tried this, but this will not always be true since clients can set their periods up differently:
Strtomember("[Period].[Fin Period].&[" + FORMAT(IIF(MONTH(NOW()) <= 6, DATEADD("yyyy", -1, NOW()), NOW()), "yyyy") + "07]")
Any Ideas?
