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
Anonymous
Not applicable

measure that disregards weekend

HI all,

 

My data is only popultaed Monday-friday. I'm trying to create a measure that will allow me to retrieve data from my facts table. 

If I use the below as a measure and alternate the offset to -1 (Tue-Fri) and -3 on a monday the card returns the desired result.

 

 

Measure = 
CALCULATE(SUM('Production stats'[Daily total Packs Per Hr (Line 2) ]),FILTER('Date','Date'[CurrDayOffset]=-3))

 

 

 

However, when I try to create a measure that works regardless of day of the week the card result is always "(Blank)". I edit "-3" to anyvalue and it always returns "(Blank)".

 

 

Measure = 
var _FilterDates = IF(WEEKDAY(TODAY(),2)=1, TODAY()-3,TODAY()-1)
return 
CALCULATE(SUM('Production stats'[Daily total Packs Per Hr (Line 2) ]),
FILTER('Date','Date'[CurrDayOffset]=_FilterDates))

 

 

 

Production stats = facts table

Date = date table (includes current day offset as a column)

 

I cannot see anything wrong with the above measure please could someone take a look for me and advise where I am going wrong? 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have solved it using:

 

3Measure = 
IF (
    SELECTEDVALUE ( 'Date'[Day of Week Number] ) IN { 6, 7 },
    BLANK (),
    IF (
        SELECTEDVALUE ( 'Date'[Day of Week Number] ) = 1,
        CALCULATE (
            SUM ( 'Production stats'[Daily total Packs Per Hr (Line 2) ] ),
            FILTER ( ALL ( 'Date' ), 'Date'[CurrDayOffset] = ( SELECTEDVALUE ( 'Date'[Date] ) - 3 ) )
        ),
        CALCULATE (
            SUM ( 'Production stats'[Daily total Packs Per Hr (Line 2) ] ),
            FILTER ( ALL ( 'Date' ), 'Date'[CurrDayOffset] = ( SELECTEDVALUE ( 'Date'[Date] ) - 3 ) )
        )
    )
)

View solution in original post

1 REPLY 1
Anonymous
Not applicable

I have solved it using:

 

3Measure = 
IF (
    SELECTEDVALUE ( 'Date'[Day of Week Number] ) IN { 6, 7 },
    BLANK (),
    IF (
        SELECTEDVALUE ( 'Date'[Day of Week Number] ) = 1,
        CALCULATE (
            SUM ( 'Production stats'[Daily total Packs Per Hr (Line 2) ] ),
            FILTER ( ALL ( 'Date' ), 'Date'[CurrDayOffset] = ( SELECTEDVALUE ( 'Date'[Date] ) - 3 ) )
        ),
        CALCULATE (
            SUM ( 'Production stats'[Daily total Packs Per Hr (Line 2) ] ),
            FILTER ( ALL ( 'Date' ), 'Date'[CurrDayOffset] = ( SELECTEDVALUE ( 'Date'[Date] ) - 3 ) )
        )
    )
)

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.