Visual needs to show months by year month not Jan thru Dec

[Tcurtis]

I am pretty new to PowerBi and am having some difficulty understanding how to make my chart show a run as a running year and not Jan thru Dec. The month I am reporting on starts with the previous month and I need my chart to show May thru April and not January thru December. Some of my months are this year and some are last year. How can I arrange my months so they show this way?

 

[bkjohn2016]

It sounds like you need a custom calendar to accommodate your fiscal period. Try this:

https://powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/

 

[macfuller]

I'm not sure if you need a custom calendar or just appropriately sorted columns in your existing calendar.

First, the assumption is that you have a separate table identified as a calendar table with consecutive dates and related to your fact table by the date. Then you can add custom columns to the calendar table - here are some common columns I add to my calendar tables.

    InsertedDay = Table.AddColumn(ExpandHoliday, "Day", each Date.Day([Date]), type number),
    InsertedMonth = Table.AddColumn(InsertedDay, "Month", each Date.Month([Date]), type number),
    InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number),
    InsertedDoW = Table.AddColumn(InsertedYear, "DayofWeek", each Date.DayOfWeek([Date]), type number),
    InsertedQuarter = Table.AddColumn(InsertedDoW, "Quarter", each Number.ToText(Date.Year([Date])) & " Q" & Number.ToText(Date.QuarterOfYear([Date]))),
    InsertedMonthName = Table.AddColumn(InsertedQuarter, "MonthName", each Date.ToText([Date],"MMM"), type text),
    InsertedMonthYearName = Table.AddColumn(InsertedMonthName, "MonthYrName", each Date.ToText([Date],"MMM") & " " & Text.End(Number.ToText(Date.Year([Date])),2)),
    InsertedDayName = Table.AddColumn(InsertedMonthYearName, "DayName", each Date.ToText([Date],"ddd"), type text),
    InsertedWeekday = Table.AddColumn(InsertedDayName, "Weekday", each if Date.DayOfWeek([Date]) = 0 then "Weekend" else if Date.DayOfWeek([Date]) = 6 then "Weekend" else "Weekday"),
    InsertedWorkdayNum = Table.AddColumn(InsertedWorkday, "WkDayNum", each if Date.DayOfWeek([Date]) = 0 then 0 else if Date.DayOfWeek([Date]) = 6 then 0 else 1, type number),
    InsertedWeeksAway = Table.AddColumn(InsertedWorkDayNum, "Weeks Ago", each Number.RoundDown(Number.From((DateTime.Date(DateTime.FixedLocalNow())-[Date])/7))+1, type number),
    InsertedWeekOfYear = Table.AddColumn(InsertedWeeksAway, "WeekOfYear", each Date.WeekOfYear([Date])),
    InsertedYearMonth = Table.AddColumn(InsertedWeekOfYear, "YearMonth", each Number.ToText(Date.Year([Date])) & "_" & Number.ToText(Date.Month([Date]),"00")),

Once PowerQuery creates the table you will use the Sort By feature in the data model window to sort your text columns (DayName by DayofWeek, MonthYearName by YearMonth, MonthName by Month, etc.) by their numeric equivalents. At that point you're pretty much set - insert a timeline to your pivot table based on your calendar date column to filter by the date range you want and use the MonthYearName as the row value in the pivot table.