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)