Sum Status Change for each day/week/month

[Anonymous]

I currently have a table in the following format.

Date	Item ID	Status
10/1	1	closed
10/1	2	closed
10/1	3	closed
10/1	4	Draft
9/30	1	open
9/30	2	work completed
9/30	3	closed
9/29	1	open
9/29	2	work completed
9/29	3	closed
9/28	1	open
9/28	2	open
9/28	3	open

Each item is also associated to a company table where each company can have multiple items but no item can have multiple companies.

How can I get a calculation over time showing how many items each company had an item move from one status to the other among all the permutations eg.

open to open

open to work completed

open to in dispute

open to closed

work completed to open

and so on

[tamerj1]

Hi @Anonymous 
Please refer to attached sample file with the solution.

Number of Changes = 
SUMX (
    VALUES ( Company[Company] ),
    CALCULATE ( 
        SUMX ( 
            VALUES ( Company[Item ID] ),
            VAR CurrentIDTable = CALCULATETABLE ( 'Table' )
            RETURN
                SUMX ( 
                    CurrentIDTable,
                    VAR CurrentStatus = 'Table'[Status]
                    VAR CurrentDate = 'Table'[Date]
                    VAR TableBefore = FILTER ( CurrentIDTable, 'Table'[Date] < CurrentDate )
                    VAR PreviousRecord = TOPN ( 1, TableBefore )
                    VAR PreviousStatus = COALESCE ( MAXX ( PreviousRecord, 'Table'[Status] ), CurrentStatus )
                    RETURN
                        IF ( CurrentStatus <> PreviousStatus, 1 )
                )
        )
    )
)

[Anonymous]

It seems I incorrectly described what I am looking for, my apologies.

Results should show changes of each status from one to another for example:

Company A
Status Open to Open (qty)

Status Open to Work Completed (qty)

Status Open to Ready to Inspect (qty)

Work Completed to Open (qty)

so since there are 7 possible initial statuses (Draft, Open, Work Completed, Ready to Inspect, Not Approved, In Dispute and Closed) there should be 1(initial status)x7(final status)= 7potential permutations where a change can be in for each item over time.

[tamerj1]

Hi @Anonymous 

please use an example to describe the expected results based on the same sample data.