Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 like
How can i change it, Please share your comments
Thanks in advance
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
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.
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
C | D | L | Date |
0 | November, 2014 | ||
0 | November, 2014 | ||
0 | November, 2014 | ||
0 | November, 2014 | ||
0 | November, 2014 | ||
0 | November, 2014 | ||
0 | November, 2014 | ||
0 | November, 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 | ||
9605332 | March, 2014 | ||
-5178.95 | October, 2014 | ||
7292.25 | September, 2013 | ||
155936.1 | September, 2013 | ||
46040.4 | October, 2014 | ||
7851 | March, 2014 | ||
9300 | March, 2014 | ||
42103.8 | March, 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?
Best Regards,
Qiuyun Yu
By doing that it is showing drill through in the report
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |