Forum Discussion

HankScorpio2's avatar
HankScorpio2
Helper I
2 years ago

What Happens when you sum datediff?

Hi

 

I am reviewing an old PowerBi report I am redoing and believe that the numbers produce are incorrect. 

However, before I call them incorrect I wanted to understand what happens if you sum datediff. 

 

This screenshot shows the measure and data that it is using. 

You can see for example that the difference between Jan 4 2022 and Jan 14 2022 is listed as 30. When its 10 total days and 9 working days. 

 

The measure is then used in another measure to create the average days to complete or turnaround time taken. 

 

Am I correct in thinking that the numbers are totally wrong and its just blind luck that the numbers look reasonable? 

 

That all the plus and minus days somehow just work out. 

 

Need Help

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi HankScorpio2 ,

     

    Is the Create to Lodge a Measure or a column? Asking this because the context for measures is different from the one in Columns.

     

    In this case if you are using a column and you have 3 lines with the date of January 14 then you will get the 10 (datediff) * 3 rows. this error will then persist on your average.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi HankScorpio2 ,

     

    Thanks to the solution MFelix  provided, datediff() is always the difference between the first date and the second date, so it goes negative when the first date is earlier. But these calculations are correct. You can calculate their absolute values with ABS() to make sure it's positive.

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    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
    2 years ago

    Hi HankScorpio2 ,

     

    Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

     

    Best regards,
    Community Support Team_ Scott Chang