98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Customising Dates
Customising Dates
Resolved · Medium Priority · Version 2016
Wed 18 Sep 2019, 16:51Delegate Helen said...
Helen has attended:
Power BI Modelling, Visualisation and Publishing course
Customising Dates
Hi there
I'm trying to build a report request which shows percentages in the y axis and in the x axis 12 months data followed by the last point which is the percentage for the 12 months to date.
I can get the line graph working for the 12 months but have no idea how to get the 12 months to element in.
Do I have to create dates that are not dates to achieve this?
This month my month elements would be:
201809
201810
201811
201812
201901
201902
201903
201904
201905
201906
201907
201908
12 Months to 201908
Then the next month I need to change automatically if possible to:
201810
201811
201812
201901
201902
201903
201904
201905
201906
201907
201908
201909
12 Months to 201909
Hope this is possible
Helen
Fri 20 Sep 2019, 10:41Trainer Jens said...
RE: Customising Dates
Hi Helen,
Thank you for the forum question.
You will need to convert the 201908 to a date.
In Data view add a column to the table and in the formula bar write:
Date = DATE(left([header of the column where you have 201908 stored],4),right([header of the column where you have 201908 stored],2),1)
If you use the new column for the x axis of the chart you should be able to get the chart you want.
To get the last 12 month values in the chart:
Add a new column and type:
Last12 =IF('Date'[Date] <= EOMONTH ( NOW (); -12 )||'Date'[Date]>= EOMONTH ( NOW (); 0 ),FALSE,tRUE)
Where 'Date'[Date] is the primary key from your date table.
Add the column to the filter of the chart and the chart will always show the last 12 month.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Master the Power BI Custom VisualsPower BI offers a variety of custom visuals created by the community and Microsoft. Explore these visuals to enhance your reports and dashboards. Whether it's a custom map, a timeline, or a unique chart type, custom visuals can add significant value to your visualizations. |
