Seven Day Sum with Multiple Filters

[Anonymous]

Hello,

I have a measure that is meant to sum the previous seven days of a filtered table. The measure works well when a single location is selected in a slicer but I am having trouble getting the measure to create a total for the columns when there is more than one location selected. I believe I need to use the sumx function to total the column, but the results I am getting are not correct.  

I have prepared a sample file which can be downloaded at this link: https://www.dropbox.com/s/tq405siw51tmt20/Sample%20Multi%20Location.pbix?dl=0

The file has two pages, one for the single location measure, which is working correctly and a second that shows the multi-location measure, which is not working correctly.

Thank you for your help

Thank you for your help. 

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could try this formula as below:

PaidSevenDaySum Multi Location = var a=MAXX('Labor','Labor'[CalendarDate])
    return Calculate(sum(Labor[DailyLabor]),filter(ALLEXCEPT(Labor,LocationInformation[LocationID]),Labor[Labor_report_description]="Total Paid (D + I)" 
        &&'Labor'[CalendarDate]>a-7
        &&'Labor'[CalendarDate]<=a)
        )
 

Result:

Best Regards,

Lin

[Anonymous]

Yes, exactly what I needed. 

Thank you!