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

Trend Line for Bar Chart Tracking 5% Reduction on Last Year's Totals

Hi All,

 

I need to add a trend line to a bar chart which shows the targeted 5% reduction against last year's totals. So if last year's total was 100 the trend line for this year needs to be at 95. 

 

The trend line also needs to filter by Type and Sub-Type 1 & Sub-Type 2  

 

How can I do this please?

 

Thanks 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Trend Line for Bar Chart Tracking 5% Reduction on Last Year's Totals

@owdtelly , Try like , with time intelligence and date table

 

95% = CALCULATE(sum('order'[Qty]), previousyear('Date'[Date]))*.95

or

95% = CALCULATE(sum('order'[Qty]), previousyear('Date'[Date]),all('Order'))*.95

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Trend Line for Bar Chart Tracking 5% Reduction on Last Year's Totals

Thank you for your help, I have just updated my origianl post to mention some filtering I need. The trend line needs to be for a specfic type and sub-types. For example type is water and sub-types are potable mains and potable abstracted

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors