Full date into month/year

[AlKS]

Hi,  Im having difficulties trying to plot the date in month groups.  I have a date column as dd/mm/yyyy.  I want to show mm/yy on visuals but am having issues making this happen.    If I select the hierarchy as year and month it just shows the year.   If I choose month only it groups the months from different years which is not want I want.  I am relatively new to Power BI so do aplogise if this seems a simple thing.  I am assuming I need to possibly create a new column that extracts just the month and year, and then just show this on a visual as a date. 

Any help greatly appreciated.

Al

[v-qiuyu-msft]

Hi @AlKS,

Assume the sample data below:

You can use a column chart visual, then drag date column to Axis, it will generate date hierarchy automatically. You can delete the Quarter and Day, keep Year and Month. Put Amount in Values. Then expand all the level via this

If the above result doesn't meet your requirement, please clarify it.

Reference:

Drill down in a visualization in Power BI

Best Regards,
Qiuyun Yu

[juliovidigal]

Do you have a calendar table?
The ideal is to always have a calendar table, with varying columns, with various types of views, and then relate your transaction table to this calendar table.

[AlKS]

No I dont.   Im guessing if I have this I can link the date in the main data to the required date format column in the date table?  I can then choose this data to plot the visual under mm/yy

[juliovidigal]

See, all my transaction tables are listed by the date field with the calendar table.

With this I can use any column in my date table to mount a view I need.

[juliovidigal]

You need to match a date table to a full date field, if in your transaction table you only have the month and year, you need to create a calculated column by adding day (01), so you can relate. Once related, you can use any of the columns in your date table to "group" in your chart. I'm going to pass a function that I use to create the date table, and the call I make on it. You can tailor it to your need.

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", "pt-BR"), type text),
InsertMonthNameENG = Table.AddColumn(InsertMonthName , "MonthNameENG", each Date.ToText([Date], "MMMM", "en-US"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameENG, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
#"InsertDay(3)" = Table.AddColumn(InsertDayName, "Day", each Date.ToText([Date], "ddd", "EN-us"), type text),
InsertDayNameENG = Table.AddColumn(#"InsertDay(3)", "DayOfWeekNameENG", each Date.ToText([Date], "dddd", "en-US"), type text),
InsertWeekStart = Table.AddColumn(InsertDayNameENG , "WeekStart", each Date.StartOfWeek([Date]), type date),
InsertWeekEnding = Table.AddColumn(InsertWeekStart , "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable

CALL

let
Source = fncCalendar(#date(Date.Year(Date.AddYears(DateTime.LocalNow(),-2)), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())),

#date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(Date.AddDays(DateTime.LocalNow(),1))), null),
#"Added Custom" = Table.AddColumn(Source, "Label", each Date.ToText(#date(Date.Year([Date]),Date.Month([Date]),Date.Day([Date])),"dd-MMM-yy"))
in
#"Added Custom"