SUM of value based on filtered data and slicer multiple selection

[Anonymous]

I have a unique situation to filter the table based on condition and slicer and then sum of value if multiple selection in the filter.

I have a table1 with the shop name, projected sales value, and date. The date can be a past date or future date. The sample below in the image.

Table1
ShopName	ProjectedSales	Date
Shop1	100000	01-Jan-20
shop1	200000	04-Apr-20
Shop1	130000	15-Oct-20
Shop2	240000	03-Jun-20
Shop2	125000	20-Nov-20

In the slicer, there are shop names coming from another dataset.

Report has KPI card that will display the value of selected shop for closest day to today in past. if multiple selection made in slicer, it should sum of value for selected shop.

For example, 

if shop1 selected - > card should have value of 200,000 as date is 4 Apr closest to today and it is in past.

if shop2 selected -> card should have value of 240000 as date is 3 june closest to today, other is in future.

if both shop selected - > card should have value of 440000, sum of value for shop1 and shop2.

I have tried so many things but couldn't get the desired output.

I have created an extra column for days different and created measure as below to get the value but it is only working for a single selection, if multiple shops selected, it is not working. Please help me with this.

ClosestdayValueByBU = CALCULATE(

VALUES('Monthly Sales Goals by BU'[ProjectedSales]),

FILTER('Monthly Sales Goals by BU' ,'Monthly Sales Goals by BU'[DaysDifferentToToday] = min('Monthly Sales Goals by BU'[DaysDifferentToToday]))

)

I have tried with SUM instead of Values, but not adding up value for multiple selection. Please help me on this.

Rajan

[d_gosbell · Accepted solution]

You definitely need to use SUM not VALUES. The VALUES function will only return a scalar result when you have a single row in the filter context.

Given your sample data something like the following should work:

ClosestdayValueByBU = CALCULATE(
SUM('Monthly Sales Goals by BU'[ProjectedSales]),
    FILTER('Monthly Sales Goals by BU' ,
        'Monthly Sales Goals by BU'[Date] = CALCULATE(max('Monthly Sales Goals by BU'[Date]), 
                ALLEXCEPT('Monthly Sales Goals by BU','Monthly Sales Goals by BU'[ShopName]), 
                'Monthly Sales Goals by BU'[Date] <= NOW() 
            )
    )
)