Forum Discussion
Fiscal Month 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
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,
Winniz
If 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,
WinnizSo I think I'm alot closer here, from using your code
To get this to look like this I had to add Fiscal Month Name, Fiscal Month and FiscalY Year, then click on the "Expand All Down on the hiearchy" to get it to line up correctly. Question is when I do that, it shows July 01, August 02, September 03. Is there a way to prevent it from showing the month number that it would be for the fiscal year?
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?
