Unanswered

Help with DateDiff and Average

(0) Share

Report

Posted on by Pizzolati

198

I am trying to calculate the average of the differences between two dates within a SharePoint list. Here is my formula, but it is not showing any values even after removing the filters.In some cases, the values of these dates will also be empty in the list.

If(Dropdown1.SelectedText.Value="Desenvolvimento";Text(Average(

AddColumns(Filter('Entregas de Desenvolvimento'; 'Work Item'="Requisicao"&& Situação="Fechado" && And(Or(IsBlank(DatePicker_Start.SelectedDate);'Data Criado'>= DatePicker_Start.SelectedDate);

Or(IsBlank(DatePicker_End.SelectedDate); 'Data Criado' <= DatePicker_End.SelectedDate)));

"_diff"; DateDiff('Data Backlog'; 'Data Closed';TimeUnit.Days)

);

_diff

);

"0"

);Text(Average(

AddColumns(Filter('Entregas de Infra'; 'Work Item'="Requisicao"&& Situação="Fechado" && And(Or(IsBlank(DatePicker_Start.SelectedDate)'Data Criado' >= DatePicker_Start.SelectedDate);

Or(IsBlank(DatePicker_End.SelectedDate); 'Data Criado'<= DatePicker_End.SelectedDate)));

"_diff"; DateDiff('Data Backlog'; 'Data Closed';TimeUnit.Days)

);

_diff

);

"0"

))

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

v-jefferni on at

Like (0)

Report
Hi @Pizzolati ,

I cannot see the Data Backlog column from in your screenshot. In DateDiff function, it requires the two dates are neither blank, otherwise it returns blank as result.

Please try and add another criteria in your formula?

If( Dropdown1.SelectedText.Value="Desenvolvimento"; Text( Average( AddColumns( Filter( 'Entregas de Desenvolvimento'; 'Work Item'="Requisicao"&& Situação="Fechado" && !(IsBlank('Data Criado')) && And( Or(IsBlank(DatePicker_Start.SelectedDate);'Data Criado'>= DatePicker_Start.SelectedDate); Or(IsBlank(DatePicker_End.SelectedDate); 'Data Criado' <= DatePicker_End.SelectedDate) ) ); "_diff"; DateDiff('Data Backlog'; 'Data Closed';TimeUnit.Days) ); _diff ); "0" ); Text( Average( AddColumns( Filter( 'Entregas de Infra'; 'Work Item'="Requisicao"&& Situação="Fechado" && !(IsBlank('Data Criado')) && And( Or(IsBlank(DatePicker_Start.SelectedDate);'Data Criado' >= DatePicker_Start.SelectedDate); Or(IsBlank(DatePicker_End.SelectedDate); 'Data Criado'<= DatePicker_End.SelectedDate)) ); "_diff"; DateDiff('Data Backlog'; 'Data Closed';TimeUnit.Days) ); _diff ); "0" ) )

Best regards,

Was this reply helpful? Yes No
Pizzolati 198 on at

Like (0)

Report

So in some cases I'll have value and in others I won't (as in the new image I sent). So even with the change in the formula you sent, no value is sent.

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

Like (1)

Report
Hi @Pizzolati ,

If it's ok to handle the average of blank dates to be 0, then the formula would be the follows:

If( Dropdown1.SelectedText.Value="Desenvolvimento"; Text( Average( AddColumns( Filter( 'Entregas de Desenvolvimento'; 'Work Item'="Requisicao"&& Situação="Fechado" && And( Or(IsBlank(DatePicker_Start.SelectedDate);'Data Criado'>= DatePicker_Start.SelectedDate); Or(IsBlank(DatePicker_End.SelectedDate); 'Data Criado' <= DatePicker_End.SelectedDate) ) ); "_diff"; If(IsBlank('Data Backlog'); 0; DateDiff('Data Backlog'; 'Data Closed';TimeUnit.Days)) ); _diff ); "0" ); Text( Average( AddColumns( Filter( 'Entregas de Infra'; 'Work Item'="Requisicao"&& Situação="Fechado" && And( Or(IsBlank(DatePicker_Start.SelectedDate);'Data Criado' >= DatePicker_Start.SelectedDate); Or(IsBlank(DatePicker_End.SelectedDate); 'Data Criado'<= DatePicker_End.SelectedDate)) ); "_diff"; If(IsBlank('Data Backlog'); 0; DateDiff('Data Backlog'; 'Data Closed';TimeUnit.Days)) ); _diff ); "0" ) )

Best regards,

Was this reply helpful? Yes No