Calculate average based on condition

[Anonymous]

Hi Team,

How can I calculate the average in the following scenario: 

Name	Status	Date
ABC1	S1	1/15/2018
ABC1	S2	2/1/2018
ABC1	S3	2/5/2018
ABC1	S5	4/2/2018
ABC2	S1	2/3/2018
ABC2	S2	3/19/2018
ABC2	S3	4/23/2018
ABC2	S4	4/29/2018
ABC2	S5	5/3/2018

In this case, the average number of days needs to be calculated between S3 and S5, if S4 is not available. If S4 is present, the average days should be between S4 and S5. How can I write a single measure for this?

In the above example, output will be: Date diff between S3 and S5 = 56 days and Date diff between S4 and S5 = 4 days. Final Average = (56+4)/2 = 30 days

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create a calculated column as below:

S4 search = SEARCH("S4",'Table'[Status],1,0)

Then create 2 measures as below:

Measure = 
var _datediff1=DATEDIFF(CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S3"&&'Table'[Name]=MAX('Table'[Name]))),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S5"&&'Table'[Name]=MAX('Table'[Name]))),DAY)
var _datediff2=DATEDIFF(CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S4"&&'Table'[Name]=MAX('Table'[Name]))),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S5"&&'Table'[Name]=MAX('Table'[Name]))),DAY)
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])),'Table'[S4 search])
Return
IF(_sum>=1,_datediff2,_datediff1)

average = 
var _count=DISTINCTCOUNT('Table'[Name])
VAR _TOTAL=SUMX(VALUES('Table'[Name]),'Table'[Measure])
Return
DIVIDE(_TOTAL,_count)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@Anonymous Probably could do something like:

Measure = 
  VAR __FromStatus = IF("S4" IN SELECTCOLUMNS('Table',"Status",'Table'[Status]),"S4","S3")
  VAR __ToStatus = "S5"
  VAR __From = MAXX(FILTER('Table',[Status] = __FromStatus),[Date])
  VAR __To = MAXX(FILTER('Table',[Status] = __ToStatus),[Date])
RETURN
  (__From - __To) * 1.


Average = 
  VAR __Table = SUMMARIZE('Table',[Name],"Days",[Measure])
RETURN
  AVERAGEX(__Table,[Measure])