Forum Discussion

dkalina97's avatar
dkalina97
Helper I
4 years ago
Solved

Percent Complete by Working Days

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 week...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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