I've used some code from the community before and tweaked it when loading one client to pull in their custom Fiscal Year. I ended up using Peek to fill in their Fiscal Year start month and the code works when I was just working with one client with one record from my FSFYr table.
Now I've been asked to do the same with my app that contains all clients but they all don't have the same Fiscal Start Month so when I use Peek it just uses the 'FSFiscalMonth' for the first client for all clients instead of peeking for each client (AcctNum) to grab their unique start month. I've also tried ApplyMap but it did just always uses the first client used like the Peek command. Anyone solved this before?
LET vToday = Num(today());
LET vFM = Peek('FSFiscalMonth',0,'FSFYr');
//Set vFM = 9; // First month of fiscal year
Set vFD = 4; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)
MasterCalendar:
Load Distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year ex. 2015/16
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil(([EventStartDate]-StartOfFWeekOne+1)/7) as FWeekNo,
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
WeekStart(FYearStart,0,$(vFD)) as StartOfFWeekOne,
*;
Load
[EventStartDate],
YearStart([EventStartDate],0,$(vFM)) as FYearStart,
Year([EventStartDate]) as Year,
Month([EventStartDate]) as Month,
Date(Monthstart([EventStartDate]), 'MMM-YYYY') as MonthYear,
Week([EventStartDate]) as ISOWeekNo,
Dual(WeekDay([EventStartDate]),Mod(WeekDay([EventStartDate]-$(vFD)),7)+1) as WeekDay,
Day([EventStartDate]) as Day,
Date([EventStartDate], 'MM/DD') as DATEMMDD
Resident Events;
