cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Responsive Resident

## Slicing a value by day of week

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!

1 ACCEPTED SOLUTION
Resolver II

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.
Resolver II

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors