PREVIOUSDAY for workdays

[apnorona]

Hi,
I´m trying to calculate a variation of a measure only for workdays (Monday to Friday). Still, the PREVIOUSDAY formula takes all the days (Monday to Sunday), and I have the problem in the variation of Monday because I don´t have data on Sunday. I need to calculate the variation of the first workday of the week with the last workday that has data in the previous week:

I´ve tried to filter the dates without values (Saturdays and Sundays), but the PREVIUOSDAY formula calculates including the weekend:

Could anyone help me?

Regards,

Andres Norona

[zaza · Accepted solution]

Try this:

PreviousWorkday = 
    IF ( WEEKDAY ( MAX ( Table[Date] ), 2 ) = 7 ), MAX ( Table[Date] ) - 3,
    IF ( WEEKDAY ( MAX ( Table[Date] ), 2 ) = 6 ), MAX ( Table[Date] ) - 2,
    MAX ( Table[Date] ) - 1 
    ) )

[danextian]

Hi @apnorona ,

" I need to calculate the variation of the first workday of the week with the last workday that has data in the previous week"

Does this mean that If Friday has not value, then the variation should be between Thursday and Monday?

[apnorona · Accepted solution]

Hi  danextian, 

Yes, the variation should be between Thursday and Monday. For example, these cases could occur on Holliday days on any working day.

Regards,

AN

[zaza · Accepted solution]

Try this:

PreviousWorkday = 
    IF ( WEEKDAY ( MAX ( Table[Date] ), 2 ) = 7 ), MAX ( Table[Date] ) - 3,
    IF ( WEEKDAY ( MAX ( Table[Date] ), 2 ) = 6 ), MAX ( Table[Date] ) - 2,
    MAX ( Table[Date] ) - 1 
    ) )

[apnorona]

Thank you, I´m going to try it.

AN