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

[owdtelly]

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 

[amitchandak]

@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.

[owdtelly]

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