Matching targets to different months and display on chart

[Daveed1973]

I have a Power BI dashboard which I need to display monthly targets on. I would like to show some kind of line chart which would show for each month the current figure against the target figure for that month. The issue I have that for each label the targets apply against, they may start in a different month. So for example I have a SQL query which gives me data like the below.

LabelName      StartDate      TargetMonth1      TargetMonth2    TargetMonth3...

Label1              01/01/18       50                          50                         75

Label2              01/01/18       25                          25                         25

Label3              01/02/18       75                          75                         45

Label4              01/03/18       10                          10                         10

In this case, TargetMonth1 for Label1 will apply to January 2018, TargetMonth1 for Label3 will apply to February 2018, TargetMonth1 for Label4 will apply to March 2018. Then each subsequent month target will follow on from the first month target. So TargetMonth2 for Label1 will be February 2018, TargetMonth2 for Label3 will be March 2018 and so on.

Then on my chart I would like the Y axis to be the target number and the x axis to be the months. So using the data above again. The total for the targets for January 2018 will be (50+25) = 75, February will be (50+25+75) = 150 and March 2018 will be (75+25+75+10)=185.

I have colour coded the above to try and make sense where these figures are coming from.

How do I get the targets to match the months they need to apply to and then be able to display this on a chart?

[Daveed1973 · Accepted solution]

I have managed to do what I needed but had to do it in SQL as a new query.

So basically something like this...

SELECT LabelName, DATEADD(MONTH, n, StartDate), [Target]
FROM tblTarget
CROSS APPLY
(
VALUES
(0, TargetMonth1),
(1, TargetMonth2),
(2, TargetMonth3),
(11, TargetMonth12)
) v(n, [Target])

I was then able to apply the different start months to the monthly targets in this SQL view. I then was able to create a relationship between two tables in BI and end up with a line graph showing target each month against actual.

[Daveed1973]

Having slept on this, I think it will not work having the separate fields for each target. Instead I probably need to generate another table with a Label, Date, Target Quantity field.

LabelName    TargetDate    TargetQty

Label1            Jan 2018        50

Label1            Feb 2018        50

Label1            Mar 2018       75

Label2            Jan 2018        25

Label2            Feb 2018        25

Label2            Mar 2018       25

Label3            Feb 2018        75

Label3            Mar 2018       75

Label3            Apr 2018        45

and so on..

This way I can have the TargetDate field on the shared axis with the actual figure on a line chart. The questions is how do I create the target table based on the table in my first post?

[v-huizhn-msft]

Hi @Daveed1973,

If you can change your data format to the second table shown in second post. You can create a calculated column to get [MM YYYY], then add sum[value] as value level.

MM YYYY = FORMAT(Table3[TargetDate],"YYYY")&"-"&FORMAT(Table3[TargetDate],"MM")

You create a line chart, select  [MM YYYY] as Axis, sum([value]) as value, you will get result as follows.



Best Regards,
Angelia