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 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. 

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.

3 Replies

  • 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

  • dkalina97's avatar
    dkalina97
    Helper I
    4 years ago

    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's avatar
      v-xiaotang
      Community Support
      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.