Count the number of products that have moved from one status to another

[NikiTiki]

Dear forum users

Has anyone encountered tracking product status changes from period to period?
I have the following table:

id	date	status
1	2021-01	A
1	2021-02	A
1	2021-05	B
1	2021-06	C
1	2021-07	A
2	2021-01	C
2	2021-05	C
2	2021-06	A
2	2021-07	A
2	2021-08	B
3	2021-01	C
3	2021-03	A
3	2021-05	A
3	2021-06	B
3	2021-08	C

Also I have two filters that work as data at the beginning of the period and data at the end of the period (not between)
For example : start_date filter - 2021-01 and end_date 2021-05

I see that like:
Product with id = 1 on 2021-01 has status A and on 2021-05 has status B and so on.

Start_date	End_date	Id	Start	End
2021-01	2021-05	1	A	B
2021-01	2021-05	2	C	C
2021-01	2021-05	3	C	A

And as result, i wish have a table like:

start_status	end_status	Count
A	A	0
A	B	1
A	C	0
A	D	0
B	A	0
B	B	0
B	C	0
B	D	0
C	A	1
C	B	0
C	C	1
C	D	0
D	A	0
D	B	0
D	C	0
D	D	0

And for example for date 2021-02 and 2021-07

Start_date	End_date	Id	Start	End
2021-02	2021-07	1	A	A
2021-02	2021-07	2	D	A
2021-02	2021-07	3	D	D

And result table

start_status	end_status	Count
A	A	1
A	B	0
A	C	0
A	D	0
B	A	0
B	B	0
B	C	0
B	D	0
C	A	0
C	B	0
C	C	0
C	D	0
D	A	1
D	B	0
D	C	0
D	D	1

Maybe someone has already tracked this? I will be grateful for help!

[daniel79]

Here's my take on this.

First I created a table to be able to select period from and to

PeriodFromTo = 
VAR YearStart = int(left(min('status'[date]),4))
VAR YearFinish = int(left(max('status'[date]),4))
VAR Periods = SELECTCOLUMNS(GENERATESERIES(1,12),"Period",[Value])
VAR Years = SELECTCOLUMNS(GENERATESERIES(YearStart, YearFinish),"Year",[Value])

VAR Periods2 = CROSSJOIN(Years, Periods)
VAR Periods3 = SELECTCOLUMNS(Periods2,"PeriodFrom", [Year] & "-" & right("0" & [Period],2))
VAR Periods4 = SELECTCOLUMNS(Periods2,"PeriodTo", [Year] & "-" & right("0" & [Period],2))
VAR Periods5 = FILTER(CROSSJOIN(Periods3, Periods4), [PeriodFrom]<=[PeriodTo])
RETURN Periods5

Then I created a table to cross join possible status

StatusFromTo = 

VAR Statuses = CALCULATETABLE(values('status'[status]))
VAR Statuses2 = SELECTCOLUMNS(Statuses,"statusFrom",'status'[status])
VAR Statuses3 = SELECTCOLUMNS(Statuses,"statusTo",'status'[status])
VAR Statuses4 = CROSSJOIN(Statuses2,Statuses3) 
RETURN Statuses4

And finally created a meassure to calculate the products that switched from a one status to the other one

statusCount = 
VAR vPeriodFrom = CALCULATE( MIN(PeriodFromTo[PeriodFrom]), ALLSELECTED(PeriodFromTo[PeriodFrom]) )
VAR vPeriodTo = CALCULATE( max(PeriodFromTo[PeriodTo]), ALLSELECTED(PeriodFromTo[PeriodFrom]) )

VAR vStatusFrom = values(statusFromTo[statusFrom])
VAR vStatusTo = values(statusFromTo[statusTo])

VAR statuses = SUMMARIZE(FILTER('status','status'[date]<=vPeriodFrom),'status'[id],"date2",max('status'[date]))
VAR statuses2 = ADDCOLUMNS(FILTER(NATURALINNERJOIN('status',statuses),[date2]='status'[date]),"MINDATE",vPeriodFrom)

VAR statuses3 = SUMMARIZE(FILTER('status','status'[date]<=vPeriodTo),'status'[id],"date2",max('status'[date]))
VAR statuses4 = ADDCOLUMNS(FILTER(NATURALINNERJOIN('status',statuses3),[date2]='status'[date]),"MAXDATE",vPeriodTo)

VAR statuses5 = NATURALINNERJOIN(SELECTCOLUMNS(statuses2,"id",'status'[id],"status1",'status'[status]), SELECTCOLUMNS(statuses4,"id",'status'[id],"status2",'status'[status]))
RETURN COUNTROWS(FILTER(statuses5,[status1] in vStatusFrom && [status2] in vStatusTo))+0

pbix and data file in the following link.

[NikiTiki]

Hello, Daniel!

Thank you very much for such great help!

I tried to understand your solution, and I will continue...but so far without success

But is it possible to change the measure for missed dates, consider that the product is in the "D" status, that is, from 2021-02 to 2021-08

statusFrom	statusTo	statusCount
A	A	0
A	B	0
A	C	0
A	D	1
B	A	0
B	B	0
B	C	0
B	D	0
C	A	0
C	B	0
C	C	0
C	D	0
D	A	0
D	B	1
D	C	1
D	D	0

Thank you for your help!!!

[daniel79]

Hi, it is considering missed dates.

There was no "D" status in the sample data, can you please update your sample dataset and the expected result?

[NikiTiki]

status D is not in the transactional table, this status means "missing"

result table, if select periodfrom 2021-02 and periodto 2021-08

statusFrom	statusTo	statusCount
A	A	0
A	B	0
A	C	0
A	D	1
B	A	0
B	B	0
B	C	0
B	D	0
C	A	0
C	B	0
C	C	0
C	D	0
D	A	0
D	B	1
D	C	1
D	D	0

Thank you again!

[daniel79 · Accepted solution]

Ok, I get it now.

I changed the cross join status table script to this to add the D status

StatusFromTo = 

VAR Statuses = CALCULATETABLE(values('status'[status]))
VAR Statuses2 = UNION(SELECTCOLUMNS(Statuses,"statusFrom",'status'[status]),ROW("status","D"))
VAR Statuses3 = UNION(SELECTCOLUMNS(Statuses,"statusTo",'status'[status]),ROW("status","D"))
VAR Statuses4 = CROSSJOIN(Statuses2,Statuses3) 
RETURN Statuses4

And the measure to this

statusCount = 
VAR vPeriodFrom = CALCULATE( MIN(PeriodFromTo[PeriodFrom]), ALLSELECTED(PeriodFromTo[PeriodFrom]) )
VAR vPeriodTo = CALCULATE( max(PeriodFromTo[PeriodTo]), ALLSELECTED(PeriodFromTo[PeriodFrom]) )

VAR vStatusFrom = values(statusFromTo[statusFrom])
VAR vStatusTo = values(statusFromTo[statusTo])

VAR statuses = FILTER('status','status'[date]=vPeriodFrom)
VAR statuses2 = FILTER('status','status'[date]=vPeriodTo)

VAR statuses3 = VALUES('status'[id])

VAR statuses4 = NATURALINNERJOIN(NATURALLEFTOUTERJOIN(SELECTCOLUMNS(DISTINCT('status'[id]),"id",'status'[id]),SELECTCOLUMNS(statuses,"id",'status'[id],"status1",'status'[status])), SELECTCOLUMNS(statuses2,"id",'status'[id],"status2",'status'[status]))

RETURN COUNTROWS(FILTER(statuses4,COALESCE([status1],"D") in vStatusFrom && COALESCE([status2],"D") in vStatusTo))+0

I have updated the attached pbix file.

Please let me know if that works for you.

[NikiTiki]

You did an incredible job, I hope one day to reach your level!!!

Thank you!