Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    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())))

     

    Pbix as attached.

     

    Regards.

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous ,

 

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())))

 

Pbix as attached.

 

Regards.

Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Frank, 

it worked perfectly! 

 

Thank you for your help.