Count number of times a value changes from a specific value to another specific value

[BIsteht]

Hi - I have the following scenario I am tying to solve.

1. We need to track the number of times a customer status changes from one specific status (Active) to any other status (Inactive, Onboarding, Canceled).
2. We need the ability to set a date filter and have the count adjust to the number of changes within the filtered range
3. This need to be calculated at the customer level.
4. There could be a different field updated, causing an additonal history record in the table where the status field is unchanged (lines 2 and 3 below from 1/2/2019).

The 'flag' column above is basically what we need to complish. Each time the status changes from Active to another status, we need to 'flag' it. So, customer 1 is flagged on 1/2/2019 and 2/5/2019. If we set a date filter to january 2019, we would have a count of 1 for customer 1.

I've seen one simliar question and solution but it does not contain a group by clause (customer in this case). Any help would be much appreciated.

[Greg_Deckler · Accepted solution]

This is a problem that involves EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER (and "group by" essentially: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[BIsteht]

@Greg_Deckler ,

Thanks for the nudge. Your blog plus a little help from the link below got me to the end result I needed.

https://powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/

Thanks again!