Forum Discussion

Kopek's avatar
Kopek
Icon for Helper IV rankHelper IV
7 years ago
Solved

Difference between today and given date in Working Days

Hello All, I tried to serch for it, but have not found anything.   I would like to calculate difference between today date and estimated go live date in working days. Usually estimated go live is...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Kopek,

     

    Try this formula as a calculated column, please.

     

    column =
IF (
    [Estimated Go-Live] >= [Today],
    0,
    CALCULATE (
        SUM ( 'Calendar'[Weekend vs. Workday] ),
        FILTER (
            'Calendar',
            'Calendar'[Date] >= [Estimated Go-Live]
                && 'Calendar'[Date] <= [Today]
        ),
        ALL ( Table1 )
    )
)

     

    Best Regards,
    Dale

Kopek's avatar
Kopek
Icon for Helper IV rankHelper IV
7 years ago

Hi All - I have found solution using power query, and I have already counted it, but may you let me know if there is a solution using DAX measure ?

 

Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    The function you are looking for is DATEDIFF.

     

    =DATEDIFF(Provide date1 column, provide date 2 column, DAY)

     

    This returns the difference in days.

    • Kopek's avatar
      Kopek
      Icon for Helper IV rankHelper IV
      7 years ago
      Hi Nikhilmekala,
      Thanks for that, but I already tried it, and this function does not work if today (date 1) is greater than end date ( date 2).
      Also I need difference in working days (excluding weekends) , all days
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        You can put today as date2 and enddate at date1, that would get rid of the minus sign.

         

        To get the count of working days only (no Saturday and Sunday), the following may give you some ideas to improvise:

         

        In order to get the number of weekdays between date1 and date2, you first have to count the number of days (weekends+weekdays) between date1 and date2...

         

        ..and then from this count, subtract the number of Saturdays and Sundays between date1 and date2...

         

        You can use the =WEEKDAY() function to determine what day any given date was. Weekday will return a number, for example, 1 for Sunday, 2 for Monday....and 7 for Saturday. You can write a formula to count 1's and 7's between date1 and date2 to arrive at the number of the Saturdays and Sundays between date1 and date2.

         

        You will no doubt, have to create a seperate date table to achieve all this.