Forum Discussion
joshua1990
Post Prodigy
6 years agoStart Date for Fiscal Months for RETURN/ ROW Approach
Hello everybody!
I am using a RETURN-ROW approach for developing an individual calendar.
This calendar approach is structured like this:
Calendar =
VAR BaseCalendar =
CALENDAR ( DATE ( 2020; 1; 1 ); DATE ( 2020; 12; 31 ) )
RETURN
GENERATE (
BaseCalendar;
VAR BaseDate = [Date]
VAR Year =
YEAR ( BaseDate )
VAR MonthNumber =
MONTH ( BaseDate )
VAR WeekNumber =
WEEKNUM ( BaseDate )
VAR FWeek =
WEEKNUM ( BaseDate; 21 )
RETURN
ROW (
"Day"; BaseDate;
"Year"; Year;
"Month Number"; MonthNumber;
"Month"; FORMAT ( BaseDate; "mmmm" );
"Year Month"; FORMAT ( BaseDate; "yyyy-mm" );
"Day of Week"; FORMAT ( BaseDate; "dddd" );
"Day of Week Short"; FORMAT ( BaseDate; "ddd" );
"Week"; WeekNumber;
"Year-Week"; Year & "-" & WeekNumber;
"Fiscal Week"; FWeek;
"Fiscal Month Short"; SWITCH (
TRUE ();
FWeek IN { 1; 2; 3; 4 }; "Jan";
FWeek IN { 5; 6; 7; 8 }; "Feb";
FWeek IN { 9; 10; 11; 12; 13 }; "Mar";
FWeek IN { 14; 15; 16; 17 }; "Apr";
FWeek IN { 18; 19; 20; 21 }; "May";
FWeek IN { 22; 23; 24; 25; 26 }; "Jun";
FWeek IN { 27; 28; 29; 30 }; "Jul";
FWeek IN { 31; 32; 33; 34 }; "Aug";
FWeek IN { 35; 36; 37; 38; 39 }; "Sep";
FWeek IN { 40; 41; 42; 43 }; "Oct";
FWeek IN { 44; 45; 46; 47 }; "Nov";
FWeek IN { 48; 49; 50; 51; 52; 53 }; "Dec"
)
)
Now I would like to add the Start Date for each Fiscal Month into this approach.
Is there any chance to include a calculation for this within the code above?
2 Replies
- Greg_Deckler
Community Champion
Wrap an ADDCOLUMNS around your ROW?
- joshua1990
Post Prodigy
Thanks for your approach!
And what kind of calculation would you include for the start date of each fiscal month?