CALCULATE, SUMX with a FILTER

[ConnieMaldonado]

I have a date slicer and need to calculate the sum of sales based on the end date in the slicer. 

I want to calculate sales for the week ending on the Friday preceding the end date in the slicer.

I calculated the start and end date for the week I want to sum the sales:

DATE START Current Week - 1

DATE END Current Week - 1

NOW, when I use those dates to sum the sales, I get 0 in all cases.  I can't figure out what I'm doing wrong:

SALES Current Week - 1 =

CALCULATE(

SUMX(Report_Efficiency, Report_Efficiency[FinHours]),

FILTER(Report_Efficiency, Report_Efficiency[DATE] >= Report_Efficiency[DATE START Current Week - 1] && Report_Efficiency[DATE] <= Report_Efficiency[DATE END Current Week - 1])

)

The grain of the table is Date, employee. 

Help and thanks!

[ConnieMaldonado · Accepted solution]

I figured this out.

To calculate the Start date, I used the following:

DATE START Current Week - 1 =

/* Get End date in slicer  */

VAR sEndDate = MAX( 'DATE Dim'[DATE] )

/* Get weekday of date, Monday is 1 */

VAR vEndDateDay = WEEKDAY( sEndDate, 2 )

/* Calculate how many days to add to bring to Friday - I could have figured out how many to get to preceding Saturday which is the start date (weeks are Saturday to Friday), but I did it this way as I also need to calculate the same for week - 2 (preceding week).  I didn't get fancy, just used IF statements.  It works.  */

VAR vDateAddition = IF(vEndDateDay = 1, 4, IF(vEndDateDay = 2, 3, IF(vEndDateDay = 3, 2, IF(vEndDateDay = 4, 1, IF(vEndDateDay = 6, 6, IF (vEndDateDay = 7, 5, 0))))))

/* Add vDateAddition to get to Friday following end date, subtract 7 to get to prior Friday, and subtract 6 to get to Saturday preceding prior Friday.  As I said, in step above, I could have figured out how many to subtract to get to prior Friday rather than Friday following eliminating the need to subtract 7. */

VAR WeekEndingMinus1 = sEndDate + vDateAddition -7 - 6

RETURN

WeekEndingMinus1

To calculate SALES for dates during the week, I used the following:

SALES Current Week - 1 =
CALCULATE(
SUMX(TABLE, TABLE[Sales]),
FILTER(
'TABLE', TABLE[DATE] >= MAXX(TABLE,TABLE[DATE START Current Week - 1]) &&
TABLE[DATE] <= MAXX(TABLE,TABLE[DATE END Current Week - 1])
)
)