Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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!!!
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?
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!
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.
You did an incredible job, I hope one day to reach your level!!!
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |