Add target bar to clustered column chart

[hankmobley]

Hi all,

I currently have a clustered column chart showing profit figures for the last 4 financial/fiscal years, and the data is coming via direct query.  What I would like to do is to have a bar chart next to each FY bar that shows the target profit for each FY, so I can have a direct comparison between target and actual.  But the target profit data is not on the data as it is only one fixed amount per FY and is also a different amount each year.

How can I incorporate these yearly fixed target profit figures into this chart?

Thanks.

[amitchandak]

@hankmobley , if you can get a year start date or year end date added to source then you can use that with date table

refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[hankmobley]

Hi @amitchandak ,

Thanks for your reply.  I followed the steps on your linked post but I'm still having issues and I think it's because I'm using a different FY date period?

For reference, my model already has a dates table with dates that go from 01/10/2018 to 30/09/2022, it also has a FY Start Month Number of 10, ,making my financial year start on 01/10 every year.  So in my case, the profit figures for FY19 go from 01/10/2018 to 30/09/2019, and so on for each FY.  There is also a Financial Year column in my dates table classifying each transaction by FY (eg. "FY22")

Here is my data from the Target table.  I amended the start/end dates as you suggested but it still doesn't look right.  For example, Year 2019 starts on 01/10/2019 and ends on 30/09/2019:

 

Here are the results I get at the end, which look very different to the expected results:

Also, I was a bit confused on your instructions to add a Date Table, as the 2 formulas below are slightly different, one with MonthYear and one without.  Which one do I use?

This is the formula I'm currently using:

Thanks again for your help, look forward to your reply.

H