Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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
I used above query but I am not able to use time intelligent functions, Working as normal calendor table
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
thank you! Quick question, How would I get the first and last date of each fiscal month?
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
Perfect. Thanks again!
@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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |