# Counting how many customers have changed status

Hello EDNA Forum!

I’m currently working on something that has stumped me. We are running ‘progression analysis’ around how many customers are moving through our enrollment program each day. We have a simple-enough fact table with 1) customer, 2) date, 3) current status, 4) status index.

I’m trying to figure out a couple of simple questions:

• how many customers progressed status today? (or didn’t stay in the same status as yesterday)
• what is the average time in each status?

I’ve used the following logic and formulas for question 1 - using the status index as values:

Current Status =
SELECTEDVALUE( ‘Customer Status’[Status Index], 0 )

Previous Day Status =
CALCULATE( [Current Status], DATEADD( Dates[Date], -1, DAY ) )

Did status change =
IF( [Current Status] = [Previous Day Status], 0 , 1 )

Total changes =
SUMX( Dates, [Did status change] )

I’ve attached a sample of my .pbix and data set … because the way I’m trying to solve does not work when there is more than 1 customer being evaluated. Essentially I’m trying to aggregate up all of my ‘did the status change’ values to find a total status movement in a day, week, month, etc.

All help greatly appreciated!

Sample Progression P WC.pbix (135.1 KB)

Sample Progression P WC (1).pbix (140.5 KB)

Please see attached, looks like status index to status is not 1 to 1 and hence few rows a No Value as previous value

I noticed this after. Also, watch this video and see

Hi @lfPBI, you may find the following post helpful in solving this. Contains both a Power Query and DAX solution for a similar problem. I’ve implemented the Power Query solution in the sample file below.

.