using values with date from two tables

[Anonymous]

I have two tables currnetly like this:

Table1:

Date	Actual qty
1 Jan	20
1 Feb	30
1 Mar	40
1 Apr	20

Table2:

Date	Plan Qty
1 Jan	20
1 May	30
1 Aug	40

I would like to have a table where I can compare Plan Qty Vs Actual Qty. 

Currently I have created a seperate table "Dates" and linked the date fields of table 1 & 2 to "Dates".

But when I create table with Date field from "Dates" table, and Actual Qty from Table 1 and Plan Qty from Table 2, I get Plan Qty as 90 against all dates.

Not sure why it is happening.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

Maybe you can try the following steps.

New table:

Dates = CALENDAR(DATE(2021,1,1),DATE(2021,8,1))

Create the relationship between table1 and table2 in the model view as shown in the figure.

Measure:

Difference = MAX(Table1[Actual qty])-MAX(Table2[Plan Qty])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Anonymous 

Check the relationship Cardinality and aggregation of the Plan Qty column in the visual

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

New Year Power BI eCard:

https://community.powerbi.com/t5/Data-Stories-Gallery/Happy-New-Year/td-p/2266398