Business Day of Year - Help

[jamesgbrooks]

Hey All,

I am trying to create a column and/or measure (not sure which is better in this situation) that will count the business day of the year. Please see the image below. For each date row, I want an output value similar to the "day of year" column that only counts business day of year IF it is a business day. If it is not a business day, I want the "business day of year" value to be null. 

Please note that I created an "Is Business Day" function(?) that is apparently not a measure nor a column. It is depicted below.

[v-piga-msft · Accepted solution]

Hi @jamesgbrooks,

You could try with this fomula below and modify it for your requirement.

WorkdayCount =
IF (
    NOT ( WEEKDAY ( 'Day'[Date] ) IN { 1, 7 } ),
    RANKX (
        FILTER ( 'Day', NOT ( WEEKDAY ( 'Day'[Date] ) IN { 1, 7 } ) ),
        'Day'[Column],
        ,
        ASC,
        DENSE
    )
)

Here is the result.

More details you could have a reference of the attachment.

Best Regards,

Cherry

[Thejeswar]

@jamesgbrooks,

Although Calculated Column / Measure doesn't make a difference in this case, I would recommend having it as a calculated column.

and you can try using the below DAX for getting NULLS

IsBussinessDay = IF(AND(NOT(MAX(Dates[Days])="Sunday"),NOT(MAX(Dates[Days])="Saturday")),"True","")