Fiscal Calendar that month ends on last Friday of a month.

[javigold]

I need to create a fiscal calendar table that starts on April 25, 2020 for FY2021 and goes through April 28, 2022 (which is the end of FY2023).    Month-end is on the last Friday of each month...so I need the month number and quarter number and Year to line up with that.   I've tried multiple dax formulas but can't seem to get the last Friday thing...:-/

Any help would be appreciated!   

[ppm1 · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = {2021..2023},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FY"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Months", each let 
monthnums = {1..12},
recordslist = List.Transform(monthnums, each [FM = _, FQ = Number.RoundUp(_/3,0)])
in recordslist),
    #"Expanded FM" = Table.ExpandListColumn(#"Added Custom", "Months"),
    #"Expanded FM1" = Table.ExpandRecordColumn(#"Expanded FM", "Months", {"FM", "FQ"}, {"FM", "FQ"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded FM1", "MonthIndex", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Date", each let 
startdate = Date.AddMonths(#date(2020, 5, 15), [MonthIndex]),
SOM = Date.StartOfMonth(startdate),
EOM = Date.EndOfMonth(startdate),
FS = Date.AddDays(SOM, - Date.DayOfWeek(SOM, Day.Saturday)),
LF = Date.AddDays(EOM, - Date.DayOfWeek(EOM, Day.Friday)),
dateslist = List.Dates(FS, Duration.TotalDays(LF-FS)+1, #duration(1,0,0,0)),
splitweeks = List.Split(dateslist, 7) 
in 
splitweeks),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded Custom", "WeekIndex", 0, 1, Int64.Type),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Index1", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}, {"FQ", Int64.Type}, {"FM", Int64.Type}, {"FY", Int64.Type}})
in
    #"Changed Type"

Pat

[ppm1 · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = {2021..2023},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FY"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Months", each let 
monthnums = {1..12},
recordslist = List.Transform(monthnums, each [FM = _, FQ = Number.RoundUp(_/3,0)])
in recordslist),
    #"Expanded FM" = Table.ExpandListColumn(#"Added Custom", "Months"),
    #"Expanded FM1" = Table.ExpandRecordColumn(#"Expanded FM", "Months", {"FM", "FQ"}, {"FM", "FQ"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded FM1", "MonthIndex", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Date", each let 
startdate = Date.AddMonths(#date(2020, 5, 15), [MonthIndex]),
SOM = Date.StartOfMonth(startdate),
EOM = Date.EndOfMonth(startdate),
FS = Date.AddDays(SOM, - Date.DayOfWeek(SOM, Day.Saturday)),
LF = Date.AddDays(EOM, - Date.DayOfWeek(EOM, Day.Friday)),
dateslist = List.Dates(FS, Duration.TotalDays(LF-FS)+1, #duration(1,0,0,0)),
splitweeks = List.Split(dateslist, 7) 
in 
splitweeks),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded Custom", "WeekIndex", 0, 1, Int64.Type),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Index1", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}, {"FQ", Int64.Type}, {"FM", Int64.Type}, {"FY", Int64.Type}})
in
    #"Changed Type"

Pat

[parasurg]

I used above query but I am not able to use time intelligent functions, Working as normal calendor table 

[parasurg]

I used above query but I am not able to use time intelligent functions, Working as normal calendor table.PLease help me on the same. if i use YoY% it is calculating as normal calendays but not customised calendor 

[javigold]

thank you!   Quick question, How would I get the first and last date of each fiscal month?

[ppm1 · Accepted solution]

Updated with SOFM and EOFM.

let
    Source = {2021..2023},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FY"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Months", each let 
monthnums = {1..12},
recordslist = List.Transform(monthnums, each [FM = _, FQ = Number.RoundUp(_/3,0)])
in recordslist),
    #"Expanded FM" = Table.ExpandListColumn(#"Added Custom", "Months"),
    #"Expanded FM1" = Table.ExpandRecordColumn(#"Expanded FM", "Months", {"FM", "FQ"}, {"FM", "FQ"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded FM1", "MonthIndex", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "MonthRecord", each let 
startdate = Date.AddMonths(#date(2020, 5, 15), [MonthIndex]),
SOM = Date.StartOfMonth(startdate),
EOM = Date.EndOfMonth(startdate),
FS = Date.AddDays(SOM, - Date.DayOfWeek(SOM, Day.Saturday)),
LF = Date.AddDays(EOM, - Date.DayOfWeek(EOM, Day.Friday)),
dateslist = List.Dates(FS, Duration.TotalDays(LF-FS)+1, #duration(1,0,0,0)),
splitweeks = List.Split(dateslist, 7) 
in 
[SOFM = FS, EOFM = LF, Date = splitweeks]),
    #"Expanded MonthRecord" = Table.ExpandRecordColumn(#"Added Custom1", "MonthRecord", {"SOFM", "EOFM", "Date"}, {"SOFM", "EOFM", "Date"}),
    #"Expanded Date1" = Table.ExpandListColumn(#"Expanded MonthRecord", "Date"),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded Date1", "WeekIndex", 0, 1, Int64.Type),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Index1", "Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}, {"FQ", Int64.Type}, {"FM", Int64.Type}, {"FY", Int64.Type}, {"SOFM", type date}, {"EOFM", type date}})
in
    #"Changed Type"

Pat

[javigold]

Perfect.   Thanks again!

[amitchandak]

@javigold , Try like

check last two columns

Date_dim = 
var _weekday =7 //Sunday- 1, Monday -2 , Tuesday -3, Wednesday -4, Thursday -5, Friday -6, Saturday -7
var _left = 7-_weekday 
var _tab  = ADDCOLUMNS(CALENDAR(date(2020,04,25), date(2022,04,28))
,"Month Year", FORMAT([Date], "MMM-YYYY")
,"Month Year sort", FORMAT([Date], "YYYYMM")
,"Week Year sort", FORMAT([Date], "YYYY - WW")
, "Year", YEAR([Date])
,"Qtr Year" ,FORMAT([Date],"YYYY\QQ"),
"WeekDay", FORMAT([Date], "ddd")
,"Month", FORMAT([Date], "MMM")
,"Month sort", FORMAT([Date], "MM")
, "Month Start Date", eomonth([date],1)
, "Month end Date", eomonth([date],0)
)
return 
ADDCOLUMNS(_tab,
"Week Rank", RANKX(_tab, [Week Year sort],,DESC, Dense),
"Week Mn Start Date" , [Month end Date] + -1*if(WEEKDAY([Month end Date])<_weekday,WEEKDAY([Month end Date],1)+ _left,WEEKDAY([Month end Date],1)-1* _weekday),
"Week Mn End Date" , [Month Start Date] + -1*if(WEEKDAY([Month Start Date])<_weekday,WEEKDAY([Month Start Date],1)+ _left,WEEKDAY([Month Start Date],1)-1* _weekday)-1

)