Calculate previous business day transactions excluding holidays and weekends

[Anonymous]

I am currently trying to calculate the number of transations on the previous business date. I have found a way to create the calucation to ignore weekends but I am unsure how to have it also ignor weekends.

this is my current formula:

Orders Previous Day =

CALCULATE (

COUNT ( 'Transactions '[ORDER_ID] ),

'Transactions '[CUR_ORDER_STATUS_CD] = "CLOSED",

IF (

(

FORMAT ( 'Calendar'[Date] = TODAY (), "dddd" ) = "Monday"

),

'Calendar'[Date]

= TODAY () - 3,

'Calendar'[Date]

= TODAY () - 1

) ,

[v-xuding-msft]

Hi @Anonymous ,

I am confused about your formula. I create a measure to exclude weekends and cound ID.

WEEKDAY = WEEKDAY('Calendar'[Date],2)

Measure = 
CALCULATE (
COUNT ( Transactions[ORDER_ID] ),FILTER(Transactions,Transactions[CUR_ORDER_STATUS_CD] = "CLOSED"  &&
MAX('Calendar'[WEEKDAY]) <> 6  && MAX('Calendar'[WEEKDAY]) <>7))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @v-xuding-msft ,

this FORMAT ( 'Calendar'[Date] = TODAY (), "dddd" ) is changng the date into day names (like monday, tuesday, wednesday, etc.). When the current date is a Monday subtract  by 3 otherwise subtract by 1. My dashboard will not used on the weekend so i am not concerned about adding in the other sitations.

I like the way you used the filters on the date. Do you have to exclude for weekends as well?

[v-xuding-msft]

Hi @Anonymous ,

I exclude weekends through the weekday isn't equal 6 and 7. You could post some sample data and expected result that we can understand your requirement clearly.

How to Get Your Question Answered Quickly

I attached my sample that maybe can help you a little bit.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Do you have a way to exclude holidays as well?