Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.