Calculate the current business day number in the month

[A_H]

Dears,

I am new to Power BI, I have the dim date table inculde a column called "Working dayes" and the value is 1 or 0, the 1 is for working day and 0 for weekend.

I want to know the cuurent buiness day number for each day .

please check the bleow example:

Day	Working Day	I want to calculate this caloumn  Working day number
Thursday, November 1, 2018	1	1
Friday, November 2, 2018	0
Saturday, November 3, 2018	1	2
Sunday, November 4, 2018	1	3
Monday, November 5, 2018	1	4
Tuesday, November 6, 2018	1	5
Wednesday, November 7, 2018	1	6

[LivioLanzo · Accepted solution]

Hi @A_H

you need to create a month and year columns and then you can do:

=
IF (
    'Table'[Working Day] = 0,
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Working Day] ),
        ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Year] ),
        'Table'[Day] <= EARLIER ( 'Table'[Day] )
    )
)

[TAMOREIRA]

Thank you so much for this solution! I have been searching for over 9 hours. 

[A_H]

Thanks 

[PattemManohar]

@A_H Please confirm that you want to flag working days i.e (Mon - Fri) and Sat - Sun are weekends. So you want to flag working days as 1 and weekends as 0.

[A_H]

all dayes are working days except friday and the woking days coulmn is calculated what I need I want to know the current working day number in the current month.

[PattemManohar]

@A_H Please try this as a "New Column"

WorkingDayNumber = 
VAR _Rnk = RANKX(FILTER(Test80WorkinDay,FORMAT([Date],"DDDD")<>"Friday" && FORMAT([Date],"MMYYYY") = FORMAT(EARLIER([Date]),"MMYYYY") ),[Date],[Date],ASC) 
RETURN IF(FORMAT([Date],"DDDD")="Friday",0,_Rnk)

Please Note - I've flagged all Fridays as 0 (As it is a non-working day)

[A_H]

Thnaks pattemmanohar