cancel
Showing results for
Did you mean:

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

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 ) )
)
)
)``````
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 ) )
)
)
)``````

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors