Forum Discussion
calculate remaining workdays dynamically
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.
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
3 Replies
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
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.
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
