Compare only Matched Date Value

[yannaing]

Having two fact tables that has one-to-many relationship with Calendar table.
One table contains data with daily price (category would not be considered) and other table with the Monday date price data.

Monday Price Table

Date	Price
9/5/2022	100
9/12/2022	103
9/19/2022	107
9/26/2022	105
10/3/2022	102
10/10/2022	108

Daily Price Table

Date	Category	Price
9/5/2022	Vege	60
9/6/2022	Fruit	67
9/7/2022	Vege	78
9/8/2022	Fruit	67
9/9/2022	Vege	66
9/10/2022	Fruit	80
9/11/2022	Vege	50
9/12/2022	Fruit	74
9/13/2022	Fruit	58
9/14/2022	Vege	58
9/15/2022	Vege	63
9/16/2022	Vege	52
9/17/2022	Fruit	71
9/18/2022	Fruit	63
9/19/2022	Vege	78
9/20/2022	Fruit	71
9/21/2022	Fruit	78
9/22/2022	Vege	64
9/23/2022	Vege	52
9/24/2022	Fruit	63
9/25/2022	Vege	75
9/26/2022	Fruit	51
9/27/2022	Vege	59
9/28/2022	Fruit	56
9/29/2022	Fruit	76
9/30/2022	Vege	50
10/1/2022	Vege	76
10/2/2022	Vege	60
10/3/2022	Fruit	55
10/4/2022	Fruit	59
10/5/2022	Vege	79
10/6/2022	Fruit	60
10/7/2022	Vege	74
10/8/2022	Fruit	76

I would like to make comparison of this two tables by taking the value that have only matched with Monday Price Table.

Can I get some help that how to write DAX that only return data from Daily Price Table that is matching Date from Monday Price Table?
And what about if we only have the random Date data instead of every Monday data?

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table =
FILTER (
    'Daily Price',
    'Daily Price'[Date] IN DISTINCT ( 'Monday Price'[Date] )
)

[yannaing]

Hi @Jihwan_Kim Thank you for your effort.

I have tried this in Google Sheet and what I expected would be as below.

But I am struggling to be done it in Power BI.

Do you have any other suggestions?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please check the below picture and the attached file.

It is for creating a new table.

[yannaing]

Thank you for you help, @Jihwan_Kim.

[Tahreem24]

@yannaing You can create new table by writing this DAX code:

Table = SUMMARIZE(FILTER(DailyPriceTable,DailyPriceTable[Date]=RELATED(MondayPriceTable[Date])),DailyPriceTable[Date],DailyPriceTable[Category],"Daily Price", SUM(MondayPriceTable[Price]),"Monday Price",SUM(MondayPriceTable[Price]))

[yannaing]

@Tahreem24 Thank you for your effort.
The RELATED() doesn't work in mine.