Slicing a value by day of week

[AUaero]

Hi,
I'm not sure my title is all that descriptive of the problem, but here's what I'm trying to do.  My data model contains a date table (Calendar) and a fact table (Historical Quotes and Orders).  Calendar is joined to Historical Quotes and Orders on 'Calendar[DATE_DESC] = 'Historical Quotes and Orderes'[TRANSACTION_DATE].  In addition to the field DATE_DESC, Calendar also contains a field titled WEEKDAY_DESC.

I've calculated a measure to calculate the total sales for a period starting 2020-01-01 and ending 2020-03-15, defined as:

# Quoted Sales 2020 Period 1 = 
CALCULATE(
    'Historical Quotes and Orders'[# Quoted Sales],
    DATESBETWEEN(
        'Calendar'[DATE_DESC],
        DATE(2020, 1, 1),
        DATE(2020, 3, 15)
    )
)

This works fine and gives the appropriate result.  The problem comes in when I try to slice this measure by 'Calendar'[WEEKDAY_DESC].  The measure returns the total value for the period regardless of the selected value from the WEEKDAY_DESC filter.  I tried modifying the measure as follows with no luck:

# Quoted Sales 2020 Period 1 = 
CALCULATE(
    'Historical Quotes and Orders'[# Quoted Sales],
    FILTER(
        ALLEXCEPT('Calendar', 'Calendar'[WEEKDAY_DESC]),
        'Calendar'[DATE_DESC] >= DATE(2020, 1, 1) &&
        'Calendar'[DATE_DESC] <= DATE(2020, 3, 15)
    )
)

Any thoughts on what might be the issue here?

Thanks!

[th3h0bb5 · Accepted solution]

Instead of DATESBETWEEN(), declare a minimum and Maximum date as variables and then use them in the filter portion of CALCULATE().

Quoted Saled Period 1 =

VAR MaxDate = DATE(2020,3,15)

VAR MinDate = DATE(2020,1,1)

RETURN

CALCULATE(

[WhateverMeasure],

[FACT_TABLE_DATE] >= MinDate,

[FACT_TABLE_DATE] <= MaxDate

)

This will allow your Date Dim table to pass along the Weekday as a filter.