Making a visual that pulls data from two tables

[Anonymous]

Hi, I want to use a visual to show the projected and actual sales of a product by quarter such as in Table C. My data sources would be Table A and B. I am new to powerBI and would be really grateful if anyone is able to help. Thank you.

Table A
Product	Product ID	Sales Date	Stock Date	Category
A	12689	9/5/2020	4/5/2020	1
B	78563	6/5/2020	5/29/2020	2
D	87654	1/24/2020	4/22/2020	4
C	76878	3/21/2020	3/25/2020	3
C	76878	6/21/2020	9/12/2020	3
D	87654	2/5/2020	2/24/2020	4
A	12689	1/15/2020	2/23/2020	1
A	12689	12/7/2020	12/9/2020	1
B	78563	9/29/2020	4/14/2020	2
C	76878	10/2/2020	7/12/2020	3
C	76878	6/25/2020	2/25/2020	3
D	87654	4/3/2020	9/9/2020	4
D	87654	11/25/2020	6/7/2020	4
C	76878	2/24/2020	2/19/2020	3
A	12689	4/5/2020	6/9/2020	1
B	78563	2/26/2020	12/17/2020	2
B	78563	3/16/2020	8/29/2020	2
.	.	.	.	.
.	.	.	.	.
.	.	.	.	.
C	76878	4/22/2020	10/12/2020	3

Table B
Product	Quarter	Projected
A	Q1	10
A	Q2	20
A	Q3	10
A	Q4	24
B	Q1	34
B	Q2	20
B	Q3	10
B	Q4	20
C	Q1	23
C	Q2	5
C	Q3	15
C	Q4	18
D	Q1	17
D	Q2	15
D	Q3	30
D	Q4	25

Output

Table C
Product	Quarter	Projected	Actual
A	Q1	10	10
A	Q2	20	25
A	Q3	10	12
A	Q4	24	24
B	Q1	34	34
B	Q2	20	21
B	Q3	10	17
B	Q4	20	18
C	Q1	23	24
C	Q2	5	6
C	Q3	15	7
C	Q4	18	23
D	Q1	17	16
D	Q2	15	13
D	Q3	30	35
D	Q4	25	26

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Try this, use the first three columns in tableB

Then create this measure

Actual =
CALCULATE (
    COUNTROWS ( 'Table A' ),
    FILTER (
        ALL ( 'Table A' ),
        'Table A'[Product] = MIN ( 'Table B'[Product] )
            && QUARTER ( 'Table A'[Sales Date] )
                = VALUE ( RIGHT ( MIN ( 'Table B'[Quarter] ), 1 ) )
    )
)

Result

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Anonymous , In table one you can create Qtr based on any of the date

example

c

Then you can create a combine key and join both tables

Key = [Product] & "-" [QTR]

you can not get data in visual together

do in Power Query , new columns

="Q"& Number.ToText(Date.QuarterOfYear([sales Date]))

In both tables

"Q"& Number.ToText(Date.QuarterOfYear([sales Date]))

merge two tables

Append and merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-query