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.

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.