DateDiff with Filter based on specific value

[PBI5851]

Hello,

 I am trying to capture the number of accounts and how long have they been in a specific status. Since we cant use a filter within datediff , not sure how to achieve this. 

AccountID	Status	Substatus	StatusDate	CurrentFlag
A1001	Open	File	5/1/2020	0
A1001	Open	Progress	5/2/2020	0
A1001	Cap	Progress	5/10/2020	0
A1001	Ship	Carrier	8/1/2020	0
A1001	Ship	Delivered	8/5/2020	1
A1004	Open	File	7/8/2020	0
A1004	Ship	Carrier	9/1/2020	0
A1004	Ship	Delivered	9/5/2020	1
A1007	Open	File	8/15/2020	0
A1007	Ship	Carrier	9/1/2020	0
A1007	Ship	Delivered	9/3/2020	0
A1007	Ship	Returned	9/5/2020	0
A1007	Ship	Cancelled	9/10/2020	1

Primary criteria is for currentflag = 1 and Status = Ship and Substatus = Delivered, how many days has it been since today. 

1) Need to display the AccountID and the days it has been since above criteria (this is on the detail page)

2) A Count of Accounts where the days since above criteria has been more than 10 days (this will be in a summary page)

Any recommendations on how to achieve this please. 

[Anonymous · Accepted solution]

Hello @PBI5851

1)

DaysSinceDelivered = 
var _DelDate = CALCULATE(max(Table2[StatusDate]), FILTER(ALLEXCEPT(Table2,Table2[AccountID]),Table2[CurrentFlag] = "1" && Table2[Status] = "Ship" && Table2[Substatus] = "Delivered"))
Return DATEDIFF(_DelDate,TODAY(),DAY)

2)

Delivered Morethan10Days = 
CALCULATE(COUNTROWS(Table2), FILTER(ALLEXCEPT(Table2,Table2[AccountID]),Table2[CurrentFlag] = "1" && Table2[Status] = "Ship" && Table2[Substatus] = "Delivered" && DATEDIFF(Table2[StatusDate],TODAY(),DAY)>10))