Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
mgradijan
Resolver I
Resolver I

cumulative year over year comparison fiscal month ok, totals start on wrong month

Trying to show last 3 years sales and have a fiscal calendar created that has the first month being fiscal month 1 and Oct as month 12.  When creating the line chart, it is showing January as the first month for the beginning of the sales period.  

 

mgradijan_2-1599769359802.png

 

 

I have sorted month name by the fiscal month ( which is numerical 1 = Nov, 2 = Dec, 3 = Jan, etc.....) by highliting the field, then choosing sort by column to sort the month name by the fiscal month number. 

 

data table info:::

 

let
StartDate = #date(2015,1,1),
YearsToGenerate=10,
FiscalYearStartMonth=11,
BaseList=List.Dates(
StartDate,
YearsToGenerate*366,
#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(BaseList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Week of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Month Name",{"Date", "Year", "Quarter", "Month", "Month Name", "Week of Year"}),
#"Inserted Day" = Table.AddColumn(#"Reordered Columns", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Day of Week",{"Day of Week"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Fiscal Date", each Date.AddMonths([Date],13-FiscalYearStartMonth)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Fiscal Date", type date}}),
#"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([Fiscal Date]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
#"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([Fiscal Date]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([Fiscal Date]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
#"Inserted Week of Year1" = Table.AddColumn(#"Renamed Columns3", "Week of Year.1", each Date.WeekOfYear([Fiscal Date]), Int64.Type),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Week of Year1",{{"Week of Year.1", "Fiscal Week"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns4",{"Fiscal Date"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}}),
#"Renamed Columns5" = Table.RenameColumns(#"Changed Type2",{{"Month Name", "Fiscal Month Name"}, {"Fiscal Month", "Fiscal Month Number"}})
in
#"Renamed Columns5"

 

>> I know the overall functionality of the data table works because another test visual I have been using to figure this out shows data from Nov1 to Oct 31, but only monthly totals, not a cumulative sum. 

mgradijan_3-1599770370824.png

 

2 REPLIES 2
Anonymous
Not applicable

i only know a few power query functions and i probably didn't understand what your need is and where the problem lies.
Anyway this is my second chart.
I saw that it is possible to sort by the values of a column, so I created a column with the values 1,2,3 ... 12 associated with nov, dec, gen, ... oct.

 

 

image.png

 


I colored this additional column so that it doesn't show and this is the result:

 

image.png

Greg_Deckler
Super User
Super User

@edhans @ImkeF 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors