Forum Discussion
Fiscal Month Name
I'm attempting to make a Fiscal Month Name column so that I can display it by Fiscal Month in order. However I'm running into some issues here. I'm able to create Fiscal Month by Number but not the month and be able to sort by the fiscal month name (i.e. July, August, September) and so on.
let
StartDate = #date(StartYear,1,1),
EndDate = #date(EndYear,12,31),
NumberOfDays = Duration.Days( EndDate - StartDate ),
Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
FiscalMonth = (MonthNumber) =>
let
FiscalMonthNumber = if MonthNumber <= 6 then MonthNumber + 6 else MonthNumber - 6,
Output = if FiscalMonthNumber < 10 then "0" & Number.ToText(FiscalMonthNumber) else Number.ToText(FiscalMonthNumber)
in
Output,
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
#"Inserted Fiscal Year" = Table.AddColumn(#"Inserted Year", "Fiscal Year", each (if Date.Month([FullDateAlternateKey]) < 7 then "FY" & Text.End(Number.ToText([Year]), 2) else "FY" & Text.End(Number.ToText([Year]+1), 2)), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Fiscal Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
#"Inserted Fiscal Month" = Table.AddColumn(#"Inserted Month Name", "Fiscal Month", each FiscalMonth([Month]), type text),
#"Inserted Period" = Table.AddColumn(#"Inserted Fiscal Month", "Period", each "P" & FiscalMonth([Month]), type text),
#"Inserted Fiscal Quarter" = Table.AddColumn(#"Inserted Period", "Fiscal Quarter", each (if Date.Month([FullDateAlternateKey]) < 7 then "Q" & Number.ToText(Date.QuarterOfYear([FullDateAlternateKey])+2) else "Q" & Number.ToText(Date.QuarterOfYear([FullDateAlternateKey])-2)), type text),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Fiscal Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
#"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
in
#"Inserted Day Name"
Hi LyonsBI_BRL ,
You can remove the Fiscal Month field. If you want to sort the visual by the Fiscal Month field, you can select the Fiscal Month Name column and then sort it by the Fiscal Month column.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
8 Replies
LyonsBI_BRL ,You have code, that should have worked
A new column like this for month order or fiscal month
if( Date.Month([Date]) >6 , Date.Month([Date]) -6 , Date.Month([Date]) +6)
Hi LyonsBI_BRL ,
Please try the following code:
let StartYear = 2019, EndYear = 2021, StartDate = #date(StartYear,1,1), EndDate = #date(EndYear,12,31), NumberOfDays = Duration.Days( EndDate - StartDate ), Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)), FiscalMonth = (MonthNumber) => let FiscalMonthNumber = if MonthNumber <= 6 then MonthNumber + 6 else MonthNumber - 6, Output = if FiscalMonthNumber < 10 then "0" & Number.ToText(FiscalMonthNumber) else Number.ToText(FiscalMonthNumber) in Output, #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number), #"Inserted Fiscal Year" = Table.AddColumn(#"Inserted Year", "Fiscal Year", each (if Date.Month([FullDateAlternateKey]) < 7 then "FY" & Text.End(Number.ToText([Year]), 2) else "FY" & Text.End(Number.ToText([Year]+1), 2)), type text), #"Inserted Month" = Table.AddColumn(#"Inserted Fiscal Year", "Month", each Date.Month([FullDateAlternateKey]), type number), #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text), #"Inserted Fiscal Month" = Table.AddColumn(#"Inserted Month Name", "Fiscal Month", each Number.From(FiscalMonth([Month]))), #"Inserted Fiscal Month Name" = Table.AddColumn(#"Inserted Fiscal Month", "Fiscal Month Name", each Date.MonthName(#date(Number.From("20" & Text.End([Fiscal Year],2)),[Fiscal Month],1))), #"Inserted Period" = Table.AddColumn(#"Inserted Fiscal Month Name", "Period", each "P" & FiscalMonth([Month]), type text), #"Inserted Fiscal Quarter" = Table.AddColumn(#"Inserted Period", "Fiscal Quarter", each (if Date.Month([FullDateAlternateKey]) < 7 then "Q" & Number.ToText(Date.QuarterOfYear([FullDateAlternateKey])+2) else "Q" & Number.ToText(Date.QuarterOfYear([FullDateAlternateKey])-2)), type text), #"Inserted Week of Year" = Table.AddColumn(#"Inserted Fiscal Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number), #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number), #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number), #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number), #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text) in #"Inserted Day Name"If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-kkf-msft thanks for getting back to me! I tried your solution, how ever if you look closely.
Basically so July is Fiscal Month 1, August is Fiscal Month 2, September is Fiscal Month 3. And July, August, September make up Q1.
I've been playing around with the code here but haven't been able to get what I'm looking for. Any additional help would be greatly appreciated.
Thanks!
Hi LyonsBI_BRL ,
Not sure if I understand correctly. I'm actually calculating the Fiscal Month Name based on the Fiscal Month column. For example, if the Fiscal Month is "7", then the Fiscal Month Name is "July". Is this your desired output?
Best Regards,
Winniz
I am very new to PBI, and i know that the code is an export of the table that you have, however what i cannot work out is how do you go about importing it?
