cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aryan Regular Visitor
Regular Visitor

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 hierarchy.JPGremoving HierarchyHierarchy.JPGwith hierarchy

How can i change it, Please share your comments

Thanks in advance 

7 REPLIES 7
DeanHall New Member
New Member

Re: Date format

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 Regular Visitor
Regular Visitor

Re: Date format

By doing that it is showing drill through in the report

Moderator v-qiuyu-msft
Moderator

Re: Date format

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. 

 

q7.PNG

 

 

Best Regards,
Qiuyun Yu 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aryan Regular Visitor
Regular Visitor

Re: Date format

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

Moderator v-qiuyu-msft
Moderator

Re: Date format

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. 

 

w2.PNG

 

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

 

w1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aryan Regular Visitor
Regular Visitor

Re: Date format

CDLDate
0  November, 2014
0  November, 2014
0  November, 2014
0  November, 2014
  0November, 2014
  0November, 2014
  0November, 2014
  0November, 2014
6.9  October, 2014
6.9  August, 2016
6.9  August, 2016
6.9  August, 2016
2158.9  November, 2015
2158.9  November, 2015
1910.2  November, 2015
2158.9  November, 2015
1294.1  October, 2014
126  March, 2014
  9605332March, 2014
  -5178.95October, 2014
  7292.25September, 2013
  155936.1September, 2013
  46040.4October, 2014
  7851March, 2014
  9300March, 2014
  42103.8March, 2014
 172864.09 March, 2014
 171100.51 March, 2014
 136305713 October, 2014
 149754093 October, 2014
 136305713 October, 2014
 149754093 October, 2014
 679438.9 October, 2014
 680781.5 October, 2014
 679438.9 October, 2014
 0 October, 2014
 0 October, 2014
 0 October, 2014
 0 October, 2014
 0 October, 2014

i need to display a bar graph according to date

Moderator v-qiuyu-msft
Moderator

Re: Date format

Hi @aryan,

 

Is it what you expected? 

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 310 members 3,116 guests