Hi,
For a KPI-tracking I wanted to create a calculated dimension to show results for the last 5 Years and the actual Year, last 3 Quarters and actual Quarter and last 3 Months and actual Month all in one graph.
To achieve this I created a ValueList:
=ValueList(
v.FifeYearBefore
,v.FourYearBefore
,v.ThreeYearBefore
,v.TwoYearBefore
,v.OneYearBefore
,v.ActualYear
,v.ThreeQuarterBefore
,v.TwoQuarterBefore
,v.OneQuarterBefore
,v.ActualQuarter
,v.ThreeMonthBefore
,v.TwoMonthBefore
,v.OneMonthBefore
,v.ActualMonth
)
v.FifeYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-5)),'1','1'),'YYYY')
v.FourYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-4)),'1','1'),'YYYY')
v.ThreeYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-3)),'1','1'),'YYYY')
v.TwoYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-2)),'1','1'),'YYYY')
v.OneYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-1)),'1','1'),'YYYY')
v.ActualYear = date(MakeDate(Year(max([Effective Date])),1,1),'YYYY')
v.ThreeQuarterBefore = date(MakeDate(Year(addmonths(QuarterStart(max([Effective Date])),-9)),'1','1'),'YYYY')&'-Q' & ceil(month(AddMonths(QuarterStart(max([Effective Date])),-9))/3)
v.TwoQuarterBefore = date(MakeDate(Year(addmonths(QuarterStart(max([Effective Date])),-6)),'1','1'),'YYYY')&'-Q' & ceil(month(AddMonths(QuarterStart(max([Effective Date])),-6))/3)
v.OneQuarterBefore = date(MakeDate(Year(addmonths(QuarterStart(max([Effective Date])),-3)),'1','1'),'YYYY')&'-Q' & ceil(month(AddMonths(QuarterStart(max([Effective Date])),-3))/3)
v.ActualQuarter = date(MakeDate(Year(QuarterStart(max([Effective Date]))),1,1),'YYYY')&'-Q'&ceil(month(QuarterStart(max([Effective Date])))/3)
v.ThreeMonthBefore = date(AddMonths(MonthStart(max([Effective Date])),-3),'YYYY-MMM')
v.TwoMonthBefore = date(AddMonths(MonthStart(max([Effective Date])),-2),'YYYY-MMM')
v.OneMonthBefore = date(AddMonths(MonthStart(max([Effective Date])),-1),'YYYY-MMM')
v.ActualMonth = date(MonthStart(max([Effective Date])),'YYYY-MMM')
I checked all Variables and they are working but somehow in the Dimension the 'YYYY-MMM' nevers shows anything with January.
Everything else is working flawlessly.
Does anyone have experience with similar problems?
Thanks for your help.
