I have a big problem with my calender. For our company i added the fiscal year in my calendar.
When i now choose Fiscal Year 2020 i can only see the revenue for the months July, August and September but there is no June and i dont know why. There is something wrong in my code. Our Fiscal Year starts on 01.06!
When i choose Fiscal Year 2019 i can see the months from June to May and it seems it will work but im not sure.
Can you help me please?
There is the code for my calendar:
TempMinMax: LOAD Min(RechBuchDat) as MinDate, Max(RechBuchDat) as MaxDate RESIDENT Umsatz;
// Variablen für MinMax Datum SET vFiscalYearStartMonth = 6; // Number of Month Will be Here for Quarter Start LET vMinDate = Peek('MinDate', 0, 'TempMinMax'); LET vMaxDate = Peek('MaxDate', 0, 'TempMinMax'); LET vToday = $(vMaxDate);
DROP TABLE TempMinMax;
// Datensätze zwischen min und max generieren
TempCal: LOAD date($(vMinDate) + RowNo() -1) AS TempDate
AutoGenerate $(vMaxDate) - (vMinDate) +1;
// Hauptkalender Hauptkalender: LOAD TempDate AS RechBuchDat, Week(TempDate) AS Woche, Year(TempDate) AS Jahr, Dual(Month(TempDate), Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1) AS FiscalMonth, YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear, Year(AddMonths(TempDate,$(vFiscalYearStartMonth))) as [Fiscal Year], Month(TempDate) AS Monat, Day(TempDate) AS Tag, WeekDay(TempDate) AS Wochentag, 'Q' & ceil(month(TempDate) / 3 ) AS Quartal, InYearToDate(TempDate, $(vToday), 0) * -1 AS JahrAktuell, InYearToDate(TempDate, $(vToday), -1) * -1 AS JahrVorher
It looks like there's a gap in your code between FiscalYear and [Fiscal Year].
FiscalYear looks to be correct.
[Fiscal Year] is not correct. You are adding six months to your date and extracting the year from that. June + 6 months = December, so you're still in the same year as you were before. You would need to add +1 month since you're moving in the opposite direction here.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.