Forum Discussion

HALEYSTODDARD's avatar
HALEYSTODDARD
Frequent Visitor
3 years ago
Solved

Excel to DAX Date Query

I'm struggling to figure out how to translate this excel formula into Dax...    Essentially I need something to calculate "worked days" vs. "working days"    Excel version: Working Days =NETWORK...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi HALEYSTODDARD 

    Do you mean that you want a formula that automatically calculates the number of working days per month? If the current date is 2022-12-20, then calculate the working days of December? If the date is 2023-01-15, then calculate the working days of January?

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi HALEYSTODDARD 

Do you mean that you want a formula that automatically calculates the number of working days per month? If the current date is 2022-12-20, then calculate the working days of December? If the date is 2023-01-15, then calculate the working days of January?

 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HALEYSTODDARD's avatar
HALEYSTODDARD
Frequent Visitor
3 years ago

Thank you for the response! 

 

I currently have total worked days like this:

Dates[Work Days] is a column that has a 1 if it's a work day, 0 if not.

 

Total_Work_Days = TOTALMTD(sum(Dates[Work Days]),ENDOFMONTH(Dates[DATE]))
 
 
I now need to calculate "Days worked" in the month. 
 
I have something like this:
Working_Days = IF(TODAY()>ENDOFMONTH(Dates[DATE]), TOTALMTD(sum(Dates[Work Days]),ENDOFMONTH(Dates[DATE])),
CALCULATE(TOTALMTD(sum(Dates[Work Days]),ENDOFMONTH(Dates[DATE]))-(DATEDIFF(STARTOFMONTH('Dates'[DATE]),TODAY(),DAY))))
 
the underlined portion is incorrect since it's capturing weekends as well. I essentially just want how many working days in the month have passed. 
  • HALEYSTODDARD's avatar
    HALEYSTODDARD
    Frequent Visitor
    3 years ago

    Anonymous