Forum Discussion

Kopek's avatar
Kopek
Helper 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 grater than today, so then measure should shows 0.

 

I have created calendar in DAX, have marked all working days as 1, all weekends as 0, i have done relationship beetween calendar and column with data. I also tried to create measure, but withous success.

Could you please help me ?

 

I am attaching screenshots for your reference.

 

 

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

7 Replies

  • Kopek's avatar
    Kopek
    Helper 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
        Helper 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
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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
      Helper IV
      7 years ago

      Hello, Sorry for delay, I was on sick leave.

      And finally to calculate it I used Query function - not measure.

      But I think that above measure will work fine.

       

       

      Thank you everyone for your help!

       

      Regards:)