Forum Discussion

aryan's avatar
aryan
Helper I
8 years ago

Date format

Hi Everyone

i have 3 different columns i need to develop a bar chart with 3 columns aganist Date column when i place the date into date hierarchy the data is displaying properly but i need only month and year to be displayed like

(Mar-2017)can we make this change in date hierarchy. And i tried to display by removing heirarchy the bar grahs are dividing per month likeremoving Hierarchywith hierarchy

How can i change it, Please share your comments

Thanks in advance 

Custom Visuals
Dashboards
Data Modeling
Reports

7 Replies

  • DeanHall's avatar
    DeanHall
    New Member
    8 years ago

    Hi,

     

    If you add the whole Date hierachy first (YYYY / MM/ DD) and then remove just day instead of adding month only, this will give the year and month in the hierachy.  Alternately create a field that has Year and Month and use that.

     

     

  • aryan's avatar
    aryan
    Helper I
    8 years ago

    By doing that it is showing drill through in the report

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi aryan,

     

    You can create a calculated column like below: 

     

    dateupdate = FORMAT('HumanResources EmployeePayHistory'[RateChangeDate],"MMM") & "-" & YEAR('HumanResources EmployeePayHistory'[RateChangeDate]) 

     

    Then create a new table: 

    Calendar =
    DISTINCT (
    SELECTCOLUMNS ( 'HumanResources EmployeePayHistory', "NewDateKey", STARTOFMONTH ( 'HumanResources EmployeePayHistory'[RateChangeDate] ),"YM",FORMAT('HumanResources EmployeePayHistory'[RateChangeDate],"MMM") & "-" & YEAR('HumanResources EmployeePayHistory'[RateChangeDate]) )
    )

     

    Then set the column YM sort by NewDateKey. Create a relationship between these two tables based on column dateupdate and YM, drag the YM column to chart X axis. See attached pbix file. 

     

     

     

    Best Regards,
    Qiuyun Yu 

     

     

    • aryan's avatar
      aryan
      Helper I
      8 years ago

      ok but i have to take 3 different columns and my problem is when i remove hierarchy it is showing individual bars of 3 columns i want it as combained

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        8 years ago

        Hi aryan,

         

        What do you mean about "my problem is when i remove hierarchy it is showing individual bars of 3 columns i want it as combained"? From your original post, it looks like the issue is there are too many same month-year values in X-axis, in your picture, is February 2018, but you would like February 2018 disappears one time. 

         

        If you don't want to use the method provided in my previous post, please try below two methods: 

         

        1. When you put Date column without hierarchy in X axis, you can change X axis Type as Continuous instead of Categorical. If there are too many records, the x axis values will only display year values. 

         

         

        2. Drag Year and Month hierarchy to chart X axis, click Expand all down on one level in the hierarchy button, see: 

         

         

        Best Regards,
        Qiuyun Yu