For the three use cases
1.start is 8th nov and end date is 12th November there are 2 two customers (id 1 and 2) that change to Regular hence the answer is 2
2. 8th nov and end date is 10th November no one changes to regular hence 0 (in fact here id3 gooes to sleeper)
3. Expected result if date start is 11th nov and end date is 12th November is 2 NOT 1 as per the sample sheet answer!! as id1 and 3 go to regular
but i am sure the main data set will be easier to get some numbers
It's seems someone provide a solution for your requirement on your another thread, pelase check it.
number of users that change to currentstatus regular = VAR summizedTbl = SUMMARIZE ( yourTable, yourTable[customerid], "Distinct Status count", DISTINCTCOUNT ( yourTable[currentstatus] ), "status", CONCATENATEX ( yourTable, yourTable[currentstatus], "/", yourTable[reportingdate], DESC ) ) VAR rowCnt = COUNTROWS ( FILTER ( summizedTbl, [Distinct Status count] > 1 && LEFT ( [status], 7 ) = "Regular" ) ) RETURN IF ( ISBLANK ( rowCnt ), 0, rowCnt )