Urgent Help Required. Summing over Fiscal Year

[ClickNorth]

Hi All,

I require urgent help on this matter as it is project related. 

I am having an issue displaying YTD PNL as my Fiscal Year begins the 6th Apr each year. 

I have a field that says FYTD PNL. I need this to calculate the returns from the beginning of the financial year to the Max value date selected. 

That is, any date after the 6th Apr 19 would start at the 6th of Apr 19 and sum returns to the max value date selected (up to the 6th Apr 2020). 

Anything prior to  6th Apr 19 would start at 6th Apr 18 and sum returns up to a max date of 6th Apr 19.

Any help would be much appreciated. 

Thanks,

SC

[LReeve]

So the following creates a YTD Flag based on your criteria of 6th April being the 1st day of the year.  This can be place in the AutoCalendar to create a new YTD flag.

=If(Num#(Text(Date(Today(),'MDD'))) < 406,
	If(Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) < Num#(Text(Date(Today(),'MDD')))
    	OR
    Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) > 406, 1,0),
    	If(Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) > 406
    		AND
    	Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) <= Num#(Text(Date(Today(),'MDD'))), 1,0))

If you use this in visualization you can create a new variable which identifies your Max(Date) you can replace anything with Today() to be your Max Date variable. Instead of 1's and Zero's you can replace the 1's with the field you are summing like below:

=Sum(If(Num#(Text(Date($(vMaxDate),'MDD'))) < 406,
	If(Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) < Num#(Text(Date($(vMaxDate),'MDD')))
    	OR
    Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) > 406, [FieldName],0),
    	If(Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) > 406
    		AND
    	Num#(Text(Date([MeasureDate.autoCalendar.Date],'MDD'))) <= Num#(Text(Date($(vMaxDate),'MDD'))), [FieldName],0)))

Combine this with a new item in AutoCalendar called FiscalYear and you can use set expressions to calculate the fiscal year you intend to display. Example of Fiscal year below.

 If(Num#(Text(Date($1,'MDD'))) < 406, Year($1) - 1, Year($1)) AS [FiscalYear] Tagged ('$axis', '$year')

There will be much more efficient ways to do this I am sure but the above seems to have worked for me! Try and use this example until one of the geniuses turn up to offer the best solution!