Percent Complete by Working Days

[dkalina97]

I feel like this should be a relatively simple DAX formula but I am struggling. I simply want to calculate the forecasted sales by workday in a month and compare it to actual. So if there are 20 weekdays in the month, and forecasted sales were 20,000, and today is workday 10, the forecasted sales MTD would be 20,000/20x10, or 10,000 and then compare that to actual sales. I have a date table and attempted to Countrows, but could not get that to work. 

[dkalina97]

This gives me the denominator, but what about the numerator. So if today is day 10, it will calculate 10/20 *20,000 = 10,000

Then tomorrow, it will calculate 11/20 = $11,000. And then I will compare that amount to actual sales MTD.

[v-xiaotang · Accepted solution]

Hi @dkalina97 

You can try this

(1) create a column in date table,

weekday = WEEKDAY('Table'[date],2)

(2) create a measure

Measure = 
    var _allweekday = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[weekday]<6))
    var _today = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[date]<=TODAY() && 'Table'[weekday]<6))
return _today/_allweekday*20000

result

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@dkalina97 , for any two dates you can get workday as column like

COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

With Start and end date of month

COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Eomonth([Date],-1)+1 ,Eomonth([Date],0) ),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

You can also do it using calendar

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA