Display the result of friday as yesterday for monday

[Anonymous]

Hello Community,

I have a measure which calculate the count of insepection for yesterdaty based on today's date: 

Number of UD taken Yesterday =

CALCULATE (

DISTINCTCOUNT('INSPECTION LOTS DATA - Header'[Inspection lot])) ,

ALL ( 'INSPECTION LOTS DATA - Header'[UD Code] ),

USERELATIONSHIP ( 'DIM_Calendar'[Date], 'INSPECTION LOTS DATA - Header'[UD code date] ),

'DIM_Calendar'[Date] =TODAY()-1),

'INSPECTION LOTS DATA - Header'[UD Code] <> BLANK ()

)

But since I don't have Data saturday and sunday, i want to display the data of friday as yesterday of monday 

Could you help me please ?

Thanks

[Anonymous]

Thank you for you help 🙂

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understand correctly about your data model and your calendar table, but please try to write a measure something like below, for inserting a condition that is not including saturday and sunday.

Number of UD taken Yesterday =
VAR _today =
    TODAY ()
VAR _yesterdayexceptweekends =
    MAXX (
        FILTER (
            'DIM_Calendar',
            'DIM_Calendar'[Date] < _today
                && 'DIM_Calendar'[Weekdayname] <> "Saturday"
                && 'DIM_Calendar'[Weekdayname] <> "Sunday"
        ),
        'DIM_Calendar'[Date]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'INSPECTION LOTS DATA - Header'[Inspection lot] ),
        ALL ( 'INSPECTION LOTS DATA - Header'[UD Code] ),
        USERELATIONSHIP ( 'DIM_Calendar'[Date], 'INSPECTION LOTS DATA - Header'[UD code date] ),
        'DIM_Calendar'[Date] = _yesterdayexceptweekends,
        'INSPECTION LOTS DATA - Header'[UD Code] <> BLANK ()
    )