Forum Discussion
Solved
DateDiff with Filter based on specific value
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.
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))
1 Reply
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))