I have dashboards with a Master Calendar. In them, I use the whole "WeekNum" calculation. But recently, I've been given a requirement that the Fiscal Year starts the "Week of August 1st". Now for a single year, it's easy to just add/subtract the difference from the WeekNum(SomeDate) value. But my calendar extends from 2010 -> Today().
How do I tell my calendar that when its calculating WeekNum, to use the week of August 1st as WeekNumb = 1? Right now, this is the pseudo-code I've got going in my head right now.
Re: Calculate Week Num - Start Fiscal Year Aug 1st.
This was my final answer. It's kind of sloppy as you can tell by the fact I have to throw away negative Fiscal Weeks which shouldn't have been created in the first place. But from what I can tell, it lines up.
The requirement for me was: The first week of the fiscal year ends on the first Sunday in August.
Let varMinDate = Num(Makedate(2015,7,1));
Let varMaxDate = Num(Makedate(2020,12,31));
Let v2014 = Num(WeekStart((Makedate(2013,8,3))));
Let v2015 = Num(WeekStart((Makedate(2014,8,1))));
Let v2016 = Num(WeekStart((Makedate(2015,8,6))));
Let v2017 = Num(WeekStart((Makedate(2016,8,5))));
Let v2018 = Num(WeekStart((Makedate(2017,8,4))));
Let v2019 = Num(WeekStart((Makedate(2018,8,3))));
Let v2020 = Num(WeekStart((Makedate(2019,8,1))));
Let v2021 = Num(WeekStart((Makedate(2020,8,7))));
Let v2022 = Num(WeekStart((Makedate(2021,8,6))));
LOAD date($(varMinDate)+IterNo()-1) AS TempDate
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate)
LOAD DISTINCT *
WHERE FiscalDateWeek > 0
WeekStart(FiscalDate) AS [Beginning Date],
WeekDay(WeekStart(FiscalDate)) AS [Beginning Day],
WeekEnd(FiscalDate) AS [Ending Date],
WeekDay(WeekEnd(FiscalDate)) AS [Ending Day],
If( Week(FiscalDate) = Week(TempStartingDate) AND Year(FiscalDate) = Year(TempStartingDate), 1