cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pandapanda Frequent Visitor
Frequent Visitor

Report number of changes in a field

I have this data

 

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

 

customeridreportingdatetotalorderscurrentstatustotalamountordered
18-Nov-172Irregular15.65
28-Nov-173Irregular34.1
38-Nov-176Regular45
19-Nov-172Irregular15.65
29-Nov-173Irregular34.1
39-Nov-176Sleeper45
110-Nov-173Irregular20
210-Nov-173Irregular34.1
310-Nov-176Sleeper45
111-Nov-173Irregular20
211-Nov-174Regular50
311-Nov-177Irregular55
112-Nov-174Regular30
212-Nov-174Regular50
312-Nov-177Irregular55
     
 
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
   
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Report number of changes in a field


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

Capture.PNGCapture.PNGCapture.PNG

4 REPLIES 4
Super User
Super User

Re: Report number of changes in a field

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Report number of changes in a field

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.

Super User
Super User

Re: Report number of changes in a field

Hi @pandapanda

 

See the attached file here.

Hope it helps

 

Change in Status.png

 

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"
    )
)

 

Moderator Eric_Zhang
Moderator

Re: Report number of changes in a field


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

Capture.PNGCapture.PNGCapture.PNG