Combine datediff and division

[Anonymous]

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.

[Anonymous · Accepted solution]

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]

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]

Hi @Anonymous ,

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

Best Regards,

Jay

[Anonymous]

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!