calculate remaining workdays dynamically

[Applicable88]

Hello,

I have fact table of orders , where I fortunenately have also a column showing me the remaining workdays left, that I want to calculate againgst the revenue target in a KPI-Card. The revenue is a fix value we set up before a month starts, which for simplicity reason is not displayed in the table:

Weekday	Date	Ordernumber	Workdays	AccMonthYear
Tuesday	28.03.2021	3453	2	202103
Wednesday	29.03.2021	432	1	202103
Thursday	30.03.2021	1212	0	202103
Friday	31.03.2021	3453	20	202104
Saturday	01.04.2021	555	19	202104
Sunday	02.04.2021	6346	19	202104
Monday	03.04.2021	7657	19	202104
Tuesday	04.04.2021	567	18	202104
Wednesday	05.04.2021	7676	17	202104

As you can see there is a irregularity here: the last workday of a month already belongs to the next revenue month (AccMonthYear Column). 

I need a measure for a KPI-card which calculates for me the remaining workdays of today. As also displayed in the table: if today would be the 29.03.2021 it should return "1" left workday, if today would be the 05.04.2021 it should return "17" working days left.  Datatype of AccMonthYear is just a Integer, not a Date Format. The problem is....what aggregation should be used here? There is no only()-function in PowerBi.

Thank you in advance!

Best. 

[amitchandak]

@Applicable88 , check if this measure can help

Work Day left in month =
Var _end = eomonth(today(),0)
var _st = eomonth(today(),-1)+1
return
COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_st,_end),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)) -COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_st,today()),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

You can replace today with date of your table

[Applicable88]

Hello @amitchandak ,

I don't know why, but it doesnt't work out for me. Using the same fieldnames, but it doesn't recogznized them as field of the table.

Isn't there a more straightforward approach?

I know there the if-statement in PowerBi cannot distinguished single values and many values of a column when putting a column. But is something like this possible:

if(Table[Date]=today(), Workday)

Practically saying, if date of the table is equal today's date then return me the remaining workday from today on.

if something like this can rebuild/changed for a row to row approach?

Best.

[Anonymous · Accepted solution]

Hi @Applicable88 ，

Assuming that there is a date dimension table in your model, you can create a calculated column in orders table as follows. Please find the attachment for the details.

Column = 
VAR _endate =
    IF (
        'orders'[Date] = EOMONTH ( 'orders'[Date], 0 ),
        EOMONTH ( 'orders'[Date] + 1, 0 ),
        EOMONTH ( 'orders'[Date], 0 )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Date' ),
        WEEKDAY ( 'Date'[Date], 2 ) <= 5,
        DATESBETWEEN ( 'Date'[Date], 'orders'[Date], _endate )
    )

I have some questions about the sample data you gave, such as Weekday and remaining workdays column. Why the workday is Wednesday instead of Monday when the date is on 05.04.2021? And why are there only 17 remaining days? How is this calculated? According to my understanding, the weekday of 05.04.2021 should be Monday, and the remaining working days are 20 days(the part with red square)...

Best Regards