cancel
Showing results for
Did you mean:
Resolver II

## CALCULATE, SUMX with a FILTER

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!
1 ACCEPTED SOLUTION
Resolver II

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])
)
)

Resolver II

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])
)
)

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.