I'm trying to count a specific value where the "Exit Household value equals to yes.
In the formula below, I'm trying to show in a KPI, "Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD)" value of 7. The formula below returns a zero value.
Formula
=Sum(Aggr(If([Exit Date] = Max(TOTAL <[Client Id]> Floor(Num([Exit Date Adjusted])))
and Max(TOTAL <[Client Id]> Floor(Num([Entry Date]))) = [Entry Date] And
if([Exit Destination]='Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD)')
, 1, 0), [Client Id], [Exit Date],[Exit Destination]))
Pivot Table
| Exit Destination | Exited Household | Yes | No |
| Emergency shelter, including hotel or motel paid for with emergency shelter voucher (HUD) | 7 | - |
| Hospital or other residential non-psychiatric medical facility (HUD) | 2 | - |
| Hotel or motel paid for without emergency shelter voucher (HUD) | 1 | - |
| No exit interview completed (HUD) | 49 | 5 |
| Other (HUD) - | 2 | - |
| Permanent housing (other than RRH) for formerly homeless persons (HUD) | 4 | - |
| Place not meant for habitation (HUD) | 84 | 6 |
| Psychiatric hospital or other psychiatric facility (HUD) | 1 | - |
| Rental by client, no ongoing housing subsidy (HUD) | 57 | - |
| Rental by client, with other ongoing housing subsidy (HUD) | 1 | - |
| Rental by client, with RRH or equivalent subsidy (HUD) | 5 | - |
| Staying or living with family, permanent tenure (HUD) | 2 | - |
| Staying or living with family, temporary tenure (e.g., room, apartment or house)(HUD) | 24 | - |
| Staying or living with friends, temporary tenure (e.g., room apartment or house)(HUD) | 9 | 1 |
| Transitional housing for homeless persons (including homeless youth) (HUD) | 1 | - |
