Forum Discussion
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. So my formula is:
average duration= datediff(Auftragsdaten[Auftragsdatum].[date];Auftragsdaten[Lieferdatum].[date];day)/[Auftragsanzahl]
Sounds easy and works for the single components, so I get an overall duration (every datediff summed up) and an overall number of assignments. The results in power bi for these single terms are correct. But when I combine them as seen in the formula, the result is just the overall duration without the number of assignments being divided. When I replace the division by a substraction, the result is as expected.
I tried it with a simple example of just two parameters and it worked, so I think the mistake may lie in my data. But I'm wondering how the formula in single components works but not in a whole.
Maybe one of you experts had a similar case and may help me.
Thank you and best greetings from a newbie.
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.
4 Replies
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.
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?