cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Distance Calculation between two dates with a particular condition

Hi Community, 

 

I have a calendar slice, with Quarters only and I would like to calculate the sum of the IDs that: 

1) based on current selected Quarter - looks at Max date of this selection; 

2) finds the MAX of the 'Date Demand' and subtracts 15 calendar days; 

 

once the above step is done and those IDs are filtered out, I would like to get the average of days between:

1) Presentation Date and Demand Date;

 

idDate demandPresentation date
FP#0000922/03/201813/04/2018
FP#0001022/03/201817/04/2018
FP#0001122/03/201820/04/2018
FP#0001219/03/201820/04/2018
FP#0001319/03/201820/04/2018
FP#0002309/04/201816/04/2018
FP#0002409/04/201819/04/2018
FP#0002509/04/201819/04/2018
FP#0002609/04/201819/04/2018
FP#0003117/04/201823/04/2018
FP#0003317/04/201824/04/2018
FP#0003515/06/201810/07/2018
FP#0003621/06/201810/08/2018
FP#0004315/06/201824/07/2018
FP#0004421/06/201807/08/2018
FP#0004522/06/201816/07/2018
FP#0004613/06/2018 
FP#0004704/07/201826/07/2018
FP#0004821/06/201826/07/2018
FP#0004904/07/201819/07/2018
FP#0005004/07/201810/09/2018
FP#0005104/07/201817/10/2018
FP#0005209/07/201823/10/2018
FP#0005330/08/201813/09/2018
FP#0005430/08/201813/09/2018
FP#0005530/08/201808/11/2018
FP#0005630/08/201813/11/2018
FP#0005730/08/2018 
FP#0005804/10/201812/10/2018
FP#0005923/10/201822/11/2018
FP#0006023/10/201820/11/2018
FP#0006117/10/201813/11/2018
FP#0006217/10/201809/11/2018
FP#0006319/10/201820/11/2018
FP#0006423/10/2018 
FP#0006525/10/201802/11/2018
FP#0006625/10/201802/11/2018
FP#0006725/10/201802/11/2018
FP#0006806/11/2018 
FP#0006914/11/201820/11/2018
FP#0007014/11/201820/11/2018
FP#0007116/11/201820/11/2018
FP#0007221/11/201820/12/2018
FP#0007328/11/2018 
FP#0007405/12/201819/12/2018
FP#0007503/01/2019 
FP#0007614/01/2019 
FP#0007724/01/2019 
FP#0007824/01/2019 
FP#0007924/01/2019 
FP#0008024/01/2019 
FP#0008124/01/2019 
FP#0008228/01/2019 
FP#0008329/01/2019 

 

I am finding difficulties in sharing data sample as I am in a closed environment. 

Your help is much appreciated! 

 

Thank you. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Distance Calculation between two dates with a particular condition

Hi @nelson_marques ,

 

One sample for your reference. If it doesn't meet your requirement, kindly share your excepted result to me.

 

Here I created some measures to work on it.

 

Measure = var _maxsel = MAX('CALENDAR'[Date])
var _maxdateondemand = CALCULATE(MAX('Table1'[Date demand]),ALL(Table1))-15
return
IF(MAX('Table1'[Date demand])>=_maxsel && MAX('Table1'[Date demand])<=_maxdateondemand,1,0)
days = DATEDIFF(MAX('Table1'[Date demand]),MAX('Table1'[Presentation date]),DAY)
count = CALCULATE(COUNT(Table1[id]),FILTER(Table1,[Measure]=1))
reslut = DIVIDE(SUMX(FILTER(Table1,[Measure]=1),[days]),CALCULATE(COUNTROWS(Table1),FILTER(Table1,[days]<>BLANK())))

Capture.PNG

 

Pbix as attached.

 

Regards.

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted

Re: Distance Calculation between two dates with a particular condition

SUBJECT WRONG: "Distance" was a type. I would like to get the average days between those dates with those conditions. 

Many thanks. 

Community Support Team
Community Support Team

Re: Distance Calculation between two dates with a particular condition

Hi @nelson_marques ,

 

One sample for your reference. If it doesn't meet your requirement, kindly share your excepted result to me.

 

Here I created some measures to work on it.

 

Measure = var _maxsel = MAX('CALENDAR'[Date])
var _maxdateondemand = CALCULATE(MAX('Table1'[Date demand]),ALL(Table1))-15
return
IF(MAX('Table1'[Date demand])>=_maxsel && MAX('Table1'[Date demand])<=_maxdateondemand,1,0)
days = DATEDIFF(MAX('Table1'[Date demand]),MAX('Table1'[Presentation date]),DAY)
count = CALCULATE(COUNT(Table1[id]),FILTER(Table1,[Measure]=1))
reslut = DIVIDE(SUMX(FILTER(Table1,[Measure]=1),[days]),CALCULATE(COUNTROWS(Table1),FILTER(Table1,[days]<>BLANK())))

Capture.PNG

 

Pbix as attached.

 

Regards.

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Distance Calculation between two dates with a particular condition

Hi Frank, 

it worked perfectly! 

 

Thank you for your help.