Creating a Slicer from a Many to One Relationship

[mngma0102]

Hello,

I am trying to create a matrix visual showing "Open Orders" and "Forecast Value" by "MonthYear". I would like to filter the matrix using "Forecast Version" which is a text field attributed to the forecasting season (ex: Spring2024, Winter2023, etc).  The problem I am running into is the slicer only works for the "Forecast Value" and is showing all "Open Orders" for each month. 

I understand that the relationship flow is contributing to the issue but I dont know how to fix it. 

*Notes - The master data on top is all coming from one data model while Forecast Data is not 

[Alex87]

I confirm the formula provided works correctly. Make sure your relationships are made at day level

for orders I added in PQ a column start of month = Date.StartOfMonth([Order Date])

Open-Orders =

CALCULATE(

    SUM(Orders[Open Orders]),

        TREATAS(

            VALUES('Forecast Data'[Date Start of Month]),

            Orders[Start of Month])

            )

[mngma0102]

Yes,  that is correct. Here is a sample of my data

Orders

Month-Year	Order Date	Open Orders
Jul-24	9/2/2024	186294
Jul-24	8/5/2024	182313
Sep-23	9/13/2023	168064
Sep-24	11/4/2024	146798
Aug-23	9/1/2023	128584
Aug-23	8/30/2023	116872

Forecast Data:

Forecast Version	Forecast Units	Region	Month	Date Start of Month	Year	Month-Year
Winter2024 FCST 1	1471	Canada	Aug	8/1/2024	2024	Aug-2024
Winter2024 FCST 1	5420	Canada	Jul	7/1/2024	2024	Jul-2024
Winter2024 FCST 1	3086	Canada	Nov	11/1/2024	2024	Nov-2024
Winter2024 FCST 1	9886	Canada	Oct	10/1/2024	2024	Oct-2024
Winter2024 FCST 1	3554	Canada	Sep	9/1/2024	2024	Sep-2024
Winter2024 FCST 1	3081	USA	Aug	8/1/2024	2024	Aug-2024
Winter2024 FCST 1	2217	USA	Jul	7/1/2024	2024	Jul-2024
Winter2024 FCST 1	919	USA	Jun	6/1/2024	2024	Jun-2024
Winter2024 FCST 1	8952	USA	Nov	11/1/2024	2024	Nov-2024
Winter2024 FCST 1	3528	USA	Sep	9/1/2024	2024	Sep-2024
Winter2024 FCST 2	2913	Canada	Aug	8/1/2024	2024	Aug-2024
Winter2024 FCST 2	1295	Canada	Jul	7/1/2024	2024	Jul-2024
Winter2024 FCST 2	7351	Canada	Jun	6/1/2024	2024	Jun-2024

[Alex87]

Hello,

If I understand correctly you would like to push the filter Forecast Version up to Date table in order to reach Orders. Your model does not allow this, you should use DAX measures instead. It can be done, but it would be very useful if you could share a sample data for the table Forecast Data and Orders.

I would give a try to something like this:

Open-Orders =

CALCULATE(

    SUM(Orders[Open Orders]),

        TREATAS(

            VALUES('Forecast Data'[Date]),

            Orders[Order Date])

            )