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.


I hope this is helpful.
eDNA - Sample Progression PQ Solution.pbix (145.0 KB)

1 Like

@sunip thank you very much - the methodology used in the post now correctly helps pull the ‘status last period’ question for me.

The last piece is around aggregating these findings … I’ve re-attached the file and need to somehow aggregate the entire “did status change” column. SUMX isn’t doing it for me because when DAX is evaluating each row of the table, it can’t reach back into a previous day (it calculates ‘status last period’ as 0 for each day it’s evaluating)

Sample Progression P WC.pbix (141.2 KB)

@Melissa yes this solved it!! Thank you very much!

I will spend some time diving into this Power Query solution … amazing find. I’ll mark this as the solution