Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combine datediff and division

Hey everybody,   I've got an sample of assignments ("Auftragsanzahl") with a start date ("Auftragdatum") and an end date ("Lieferdatum"). What I want to know is the average duration of an assigment...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    If I understand you correctly, the situation you're experiencing might be caused by the calculation mechanism of Measure:

    Below measures might help you understand it more directly.

    Measure = SUMX(Auftragsdaten,DATEDIFF(MAX(Auftragsdaten[Auftragsdatum]),MAX(Auftragsdaten[Lieferdatum]),DAY))

diffday = DATEDIFF(MAX(Auftragsdaten[Auftragsdatum]),MAX(Auftragsdaten[Lieferdatum]),DAY)
sumdiffday = SUMX(Auftragsdaten,[diffday])

     

    The first measure actually calculated DATADIFF between MAX(Auftragsdaten[Auftragsdatum]){10/15/2019} and MAX(Auftragsdaten[Lieferdatum]{11/15/2109}, whith is 31+31+31=93.

    The second measure and third measure calculated DATADIFF for each rows, which is 61+51+31=143.
    Hopefully works for you.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

Hi Anonymous ,

 

thanks for the explanation, I see what's the matter. But as I try it with your suggestion the result is the same as before. Even if I limit my data to just 3 listings, I get the wrong result (the result is all the durations summed up without the division being made. So is there any other way, I could do the division?

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

Could you please share some sample data to me if you don't have any Confidential Information?

 

Best Regards,

Jay

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey Anonymous ,

     

    thanks for your reply and sorry for my very late answer , I was out of the office for a few days. After rethinking what you showed I had another look at my data and could find a mistake, that led to the wrong conclusions. For now it works good.

     

    Thanks!