Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago

Calculating DataDiff in Workingdays with blank values

Goodday brainiacs, i'm in need of your help once again I want to calculate the average time (in workingdays) it takes to fix a complaint or a damage created by our teams   I have a date table with...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi RonaldvdH ,

I tried to create two tables that you described, but the information you gave is too little. I can’t create relationship between the two tables.

Maybe you can give me more columns , then, I can make a data model and calculate the dateDiff in workingdays.

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

RonaldvdH's avatar
RonaldvdH
Post Patron
6 years ago

v-lionel-msft thanks for your help.

 

The 2 tables have a relationship based on Date (in the Date Table) and de date in the column 'Binnenkomst' (in the Damage table)

 

The damage table consists of addresses and dates when a damage is registrered (column 'Binnenkomst') and when it's solved (column 'Afgehandeld')but also a damage type and category.
The Date table consists of Date, weekday, isWorkday, DayOfWeek and Week number

 

Adres           Binnenkomst           Afgehandeld          Calculated column

A                  01-03-19                 03-03-19                Date Afgehandeld - Date Binnenkomst

B                  15-08-19             

C                  31-10-19                 01-11-19

D                  16-05-18                 16-08-18 

E                   06-12-19

 

Basically it needs to calculate that IF afgehandeld = BLANK() then calculated as if Afgehandeld is TODAY() and still returns an value based on workingdays

 

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi RonaldvdH ,

    Is the result like this?

    DateDiff = 
IF(
    MAX([Afgehandeld   ]) <> BLANK(),
    DATEDIFF(
        MAX([Binnenkomst]),
        MAX([Afgehandeld   ]),
        DAY
    ),
    DATEDIFF(
        MAX([Binnenkomst]),
        TODAY(),
        DAY
    )
)

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • RonaldvdH's avatar
      RonaldvdH
      Post Patron
      6 years ago

      v-lionel-msft thanks for the response 🙂 

       

      However the result now is that every value is 0,00 and that can't be correct but i feel that is should work.

      Both column are in the same table but i need to calculated based on working days so there should be a relation with de DATE table right ?