Report number of changes in a field

[pandapanda]

I have this data

How can I show a report with the number of users that change to currentstatus =regular from any previous status?

customerid	reportingdate	totalorders	currentstatus	totalamountordered
1	8-Nov-17	2	Irregular	15.65
2	8-Nov-17	3	Irregular	34.1
3	8-Nov-17	6	Regular	45
1	9-Nov-17	2	Irregular	15.65
2	9-Nov-17	3	Irregular	34.1
3	9-Nov-17	6	Sleeper	45
1	10-Nov-17	3	Irregular	20
2	10-Nov-17	3	Irregular	34.1
3	10-Nov-17	6	Sleeper	45
1	11-Nov-17	3	Irregular	20
2	11-Nov-17	4	Regular	50
3	11-Nov-17	7	Irregular	55
1	12-Nov-17	4	Regular	30
2	12-Nov-17	4	Regular	50
3	12-Nov-17	7	Irregular	55
	Expected result if date start is 8th nov and end date is 12th November is 2
	Expected result if date start is 8th nov and end date is 10th November is 0
	Expected result if date start is 11th nov and end date is 12th November is 1

[Eric_Zhang · Accepted solution]

---

@pandapanda wrote:

I have this data

How can I show a report with the number of users that change to currentstatus =regular from any previous status?

customerid	reportingdate	totalorders	currentstatus	totalamountordered
1	8-Nov-17	2	Irregular	15.65
2	8-Nov-17	3	Irregular	34.1
3	8-Nov-17	6	Regular	45
1	9-Nov-17	2	Irregular	15.65
2	9-Nov-17	3	Irregular	34.1
3	9-Nov-17	6	Sleeper	45
1	10-Nov-17	3	Irregular	20
2	10-Nov-17	3	Irregular	34.1
3	10-Nov-17	6	Sleeper	45
1	11-Nov-17	3	Irregular	20
2	11-Nov-17	4	Regular	50
3	11-Nov-17	7	Irregular	55
1	12-Nov-17	4	Regular	30
2	12-Nov-17	4	Regular	50
3	12-Nov-17	7	Irregular	55
	Expected result if date start is 8th nov and end date is 12th November is 2
	Expected result if date start is 8th nov and end date is 10th November is 0
	Expected result if date start is 11th nov and end date is 12th November is 1

---

@pandapanda

You can try a measure as below. See more in the attached pbix file.

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 )

[Ashish_Mathur]

Hi,

I have a small clarification to seek.  When you say that the date range is 8/Nov to 12/Nov, then do you only want to compare the status on only the opening and closing dates or see the ones in the intervening period as well?  So for example, if the status of ID3 was regular on 12/Nov, then should that id be excluded when studying the dates range 8/Nov to 12/Nov (he stayed as regular on both those dates) or should he be included because his status changed from Irregular on 11/Nov to regular on 12/Nov.

Please clarify.

[Zubair_Muhammad]

Hi @pandapanda

See the attached file here.

Hope it helps

Following MEASURE is used

My_Count =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                TableName,
                TableName[customerid],
                "Status Start", CALCULATE (
                    VALUES ( TableName[currentstatus] ),
                    FILTER (
                        TableName,
                        TableName[reportingdate] = MIN ( TableName[reportingdate] )
                    )
                ),
                "Status End", CALCULATE (
                    VALUES ( TableName[currentstatus] ),
                    FILTER (
                        TableName,
                        TableName[reportingdate] = MAX ( TableName[reportingdate] )
                    )
                )
            ),
            "Change in Status", IF ( [Status End] = "Regular" && [Status Start] <> "Regular", "Yes", "No" )
        ),
        [Change in Status] = "Yes"
    )
)

[Greg_Deckler]

If you do not have millions of rows, something along these lines might work:

StatusChanged = var nextStatus = MINX(FILTER(table,table[customerid]=EARLIER(table[customerid])&&table[currentstatus]<>EARLIER(table[currentstatus])&&table[reportingdate]>EARLIER(table[reportingdate])),table[currentstatus])
RETURN IF(nextStatus <> [currentstatus],1,0)

You would then have a column that you could count.