Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Quick q, I am trying to create a code so that regardless of the month you select within your fiscal year, it will always return the first month of that fiscal year (April). So if I selected October 2018, it will return the data for April 2018. Does anyone know how to write the code for this?
Currently my master calendar looks like this:
MasterCalendar:
Load Distinct
TempDate as Date,
Year(TempDate) as Year,
Month(TempDate) as Month,
If(Month(TempDate)=$(vFM),1,0) as AprilFlag,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
Year(TempDate) + If(Month(TempDate)>=$(vFM),1,0) as [Fiscal Year],
Date(monthstart(TempDate), 'YYYYMM') as YearMonth,
Year(TempDate) + If(Month(TempDate)=$(vFM),1,0) &
(Month(TempDate)= 'Apr') as YearAprilFlag,
Year(TempDate) + If(Month(TempDate)>=$(vFM),1,0) &
if(Month(TempDate)= 'Apr',01,
if(Month(TempDate)= 'May',02,
if(Month(TempDate)= 'Jun',03,
if(Month(TempDate)= 'Jul',04,
if(Month(TempDate)= 'Aug',05,
if(Month(TempDate)= 'Sep',06,
if(Month(TempDate)= 'Oct',07,
if(Month(TempDate)= 'Nov',08,
if(Month(TempDate)= 'Dec',09,
if(Month(TempDate)= 'Jan',10,
if(Month(TempDate)= 'Feb',11,
if(Month(TempDate)= 'Mar',12,
0)
)
)
)
)
)
)
)
)
)
)
) as [Fiscal YearMonth]
Thanks a lot in advance
For anyone who is interested, after going through it a few times I found the solution
I created two variables:
vMaxFiscalYear: Max([Fiscal Year])
vSelectApril: Min({<Date=,Month=,Year=,YearMonth=,[Fiscal YearMonth]=,[Fiscal Year]={"=$(=vMaxFiscalYear)"}>}[YearMonth])
May be this
MonthName(YearStart(Max(DateField), 0, 4))
Thank you very much for your reply Sunny.
Unfortunately this wasn't holding April fixed when I selected different months in the Fiscal Year. However, after going over it a few times I found the solution!
For anyone who is interested, after going through it a few times I found the solution
I created two variables:
vMaxFiscalYear: Max([Fiscal Year])
vSelectApril: Min({<Date=,Month=,Year=,YearMonth=,[Fiscal YearMonth]=,[Fiscal Year]={"=$(=vMaxFiscalYear)"}>}[YearMonth])