Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aryan
Helper I
Helper I

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 Hierarchyremoving Hierarchywith hierarchywith hierarchy

How can i change it, Please share your comments

Thanks in advance 

7 REPLIES 7
v-qiuyu-msft
Community Support
Community Support

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.

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

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.

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

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.
aryan
Helper I
Helper I

By doing that it is showing drill through in the report

DeanHall
New Member

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.