Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
AUaero
Responsive Resident
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
th3h0bb5
Resolver II
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.

View solution in original post

1 REPLY 1
th3h0bb5
Resolver II
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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