Comparing monthly aggregated data from one table to monthly targets in another table

[NManku]

Hi there,

I'm relatively new to Power BI but learning!

I have two sets of data which I want to link together, but I'm struggling to get to exactly what I want.

In one table I have donation amount and dates, and in another I have the monthly target for total donations.

I want to show in one chart how we're doing actual vs. target (see examples below).

Any help would be massively appreciated.

Cheers,

Nic

Table 1

Donation Amount	Donation Date
5	15/04/17
10	23/04/17
20	01/05/17
10	18/05/17
5	02/06/17
7	30/06/17

Table 2

Month	Target
Apr-17	20
May-17	30
Jun-17	12

Desired outcome

Month	Actual Donation Total	Target Donation Total	Difference
Apr-17	15	20	-5
May-17	30	30	0
Jun-17	12	15	-3

[DAX0110 · Accepted solution]

Hi @NManku, this is a typical many-to-1 scenario.  You would begin (in Data View) by adding a calculated column to Table 1 that transforms the donation date-month-year into month-year, which is the same format and data type as Table 2's "month" column.

For example, if Table 2's "month" column is a "text" type, then the calculated column should be text as well.  However, if it's a date type that uses 1-Apr-2017 to represent the whole month, then the calculated column should map DD-Apr-2017 to 1-Apr-2017.

Next, in the Relationship View, link this calculated column to the "month" column in Table 2.

Then, you'd add three measures :

Actual Donation Total := SUM( 'Table 1'[Donation Amount] )

Target Domation Total := AVERAGE('Table 2'[Target] )

Difference := [Actual Donation Total] - [Target Donation Total]

Finally, go to Report View, add a Table visual,  and put "Month" from Table 2 on the rows, and the three measures on the Values.

[Aimeeclaird]

Hi @DAX0110 

This thread has been really helpful to me as i'm trying to solve a similar requirement. 

There is a slight difference in my data in that I have a variety of different contracts, each sale was completed under a contract and i'd like to lookup the target value for that contract. For example;

Target table = 

Contract	Target Value	Target Period
1	1000000	Jan 2020
2	1500000	Feb 2020
3	2000000	March 2020
1	500000	Jan 2020
2	1000000	Feb 2020
3	1750000	March 2020
1	2500000	Jan 2020
2	2000000	Feb 2020
3	1750000	March 2020

Sales Data = 

Contract	Sale Value	Date of Sale
1	500000	01/01/2020
2	450000	17/01/2020
3	340000	22/03/2020
1	250000	4/02/2020
2	345000	5/03/2020
3	333000	12/03/2020
1	125670	30/03/2020
2	400000	30/01/2020
3	350000	15/03/2020

Please can you advise how I can adapt the solution you've provided below to also consider which contract we need to lookup?

Many thanks in advance!