Forum Discussion

AlKS's avatar
AlKS
Regular Visitor
9 years ago

Full date into month/year

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

Need Help

5 Replies

  • juliovidigal's avatar
    juliovidigal
    Icon for Advocate I rankAdvocate I
    9 years ago

    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's avatar
      AlKS
      Regular Visitor
      9 years ago

      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's avatar
        juliovidigal
        Icon for Advocate I rankAdvocate I
        9 years ago

        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"

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    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