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

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
149
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

Code:
    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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top