Filter a table based other filtered table

[Jorrafer25]

Hello community!

I need help filtering a table based on the selected values from another table. My company receives every few days the forecast about how many items should produce. That demand would look like this:

The problem is that we receive different versions every time that the request arrives. For example, we receive in the current week a forecast version that I will call AAA. The next week we will receive version BBB, and the following week version CCC, and so on. Like this:

The table where this info is stored looks like this:

Since we receive only the forecast from the current date onwards, we need to consider the exported volumes to have the historical data to compare. The exported volumes looks like this:

The date until we should consider the exported volume instead of the forecast is what we call the cut-off date, and we have it in a table that contains the Version and the Product.

My goal is to be able to compare different versions between each others. That is why in my power bi I created two filters to select the version and a third one to chose the Product:

The table for the first filter is calculated with the following formula:

Selection1 = Values(Forecast[Version])

The same for the other filter:

Selection2 = Values(Forecast[Version])

The measures in the tables are:

ForecastQtySelection1 = 

VAR VersionSelected = Values(Selection1[Version])
VAR ForecastQuantity =
    Calculate(
        SUM(
            Forecast[Quantity]
        ),
        Filter(
            Forecast,
            Forecast[Version] in VersionSelected
        )
    )
Return ForecastQuantity

And it's the same for Selection2, just keep in mind that it should be based on the other filter.

So far, with those formulas, I could get and compare the forecasts, but now I need to consider the exported volume from the cut-off date backward.

I need to get the following scenarios:

-Selection: Prod Z & AAA (cut off date 1/1/2022 [so no exported volumes are considered])

-Selection: Prod Z & BBB (cut off date 4/1/2022 [so I need to consider the exported volumes until 4/1/2022 included])

-Selection: Prod Y & CCC (cut off date 10/1/2022):

-Selection: Prod Y & Prod Z & CCC (only the first table, the second table is just to explain the result)

I tried to calculate the volume filtering by the maximum selected date, but it fails.

This is the link to the pbix file: LINK 

Thanks in advance!

[v-jialluo-msft · Accepted solution]

^{Hi @Jorrafer25 ,}

^{Add a new measure to the original one}

Forecast =
SUMX (
    SUMMARIZE (
        FILTER (
            ALL ( 'CutOffDates' ),
            [CutOffDate] < MAX ( 'Calendar'[Date] )
                && [Version] IN VALUES ( Selection1[Version] )
        ),
        'CutOffDates'[Version],
        'CutOffDates'[PartNo],
        'CutOffDates'[CutOffDate],
        "1",
            CALCULATE (
                SUM ( 'Forecast'[Quantity] ),
                FILTER (
                    'Forecast',
                    'Forecast'[PartNo] = EARLIER ( 'CutOffDates'[PartNo] )
                        && 'Forecast'[Version]
                            IN VALUES ( Selection1[Version] )
                                && 'Forecast'[Date] > EARLIER ( 'CutOffDates'[CutOffDate] )
                )
            )
    ),
    [1]
)

^{The result is as follows :}

^{Best Regards,}

^{Gallen Luo}

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

[v-jialluo-msft]

^{Hi @Jorrafer25 ,}

^{Please follow these steps:}

^{(1) Edit table relationships}

^{(2) Add a new measure}

Export =

SUMX (

    SUMMARIZE (

        FILTER (

            ALL ( 'CutOffDates' ),

            [CutOffDate] > MAX ( 'Calendar'[Date] )

                && [Version] IN VALUES ( Selection1[Version] )

        ),

        [Version],

        [PartNo],

        [CutOffDate],

        "1",

            CALCULATE (

                SUM ( 'Exported'[Quantity] ),

                FILTER (

                    'Exported',

                    [PartNo] = EARLIER ( 'CutOffDates'[PartNo] )

                        && [Date] <= EARLIER ( 'CutOffDates'[CutOffDate] )

                )

            )

    ),

    [1]

)

^{(3) The result is as follows :}

^{Best Regards,}

^{Gallen Luo}

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

[Jorrafer25]

Hello Gallen Luo!

Thanks so much for the answer! I think it's really really close for what I need. Unfortunatelly, I think It's failing in the measure ForecastQtySelection1. This measure should contains only the quantities from the cut off dates onwards. 

What I need is:

Thanks so much!

Best Regards