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.
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.
@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)
