Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
the task: I have a set of data with the information of the move in dates and move out dates of production batches, and I need to check which batches violate the "first in first out" rule. And it should be on plant and product level.
In excel, I would make a pivot table and order the move in dates from oldest to newest, and then I can just compare the move out dates.
Is it possible to do something similar in Power BI? I have googled a lot but still have no idea how I should start.
Thanks a lot in advance!
Solved! Go to Solution.
hi, @Cynwaa
After my research, There are two ways for you to refer to:
a)
Step1:
In Edit Queries, Duplicate the basic table.
Then add the index column for two table
Basic table index column is starting at 1 and increment is 1
Duplicate table index column is starting at 0 and increment is 1
Step2:
Merge two tables as below:
Step3:
Expand the table and Close&Apply
Step4:
Add a calculate column by this formula
Result = IF(AND(Table1[Plant]=Table1[Table1 (2).Plant],Table1[Product]=Table1[Table1 (2).Product]),IF(Table1[move out date]>Table1[Table1 (2).move out date],"Violation",BLANK()),BLANK())
Result:
b)
Step1:
In Edit Queries, add an index column for the basic table
Step2:
Close&Apply
Step3:
Add a calculate column by this formula
Result = IF ( AND ( Table6[Plant] = CALCULATE ( MAX ( Table6[Plant] ), FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 ) ), Table6[Product] = CALCULATE ( MAX ( Table6[Product] ), FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 ) ) ), IF ( Table6[move out date] > CALCULATE ( MAX ( Table6[move out date] ), FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 ) ), "Violation", BLANK () ), BLANK () )
Result:
here is pbix, please try it.
https://www.dropbox.com/s/mtpjwie1vanrt9i/Inventory%20first%20in%20first%20out.pbix?dl=0
Best Regards,
Lin
hi, @Cynwaa
After my research, There are two ways for you to refer to:
a)
Step1:
In Edit Queries, Duplicate the basic table.
Then add the index column for two table
Basic table index column is starting at 1 and increment is 1
Duplicate table index column is starting at 0 and increment is 1
Step2:
Merge two tables as below:
Step3:
Expand the table and Close&Apply
Step4:
Add a calculate column by this formula
Result = IF(AND(Table1[Plant]=Table1[Table1 (2).Plant],Table1[Product]=Table1[Table1 (2).Product]),IF(Table1[move out date]>Table1[Table1 (2).move out date],"Violation",BLANK()),BLANK())
Result:
b)
Step1:
In Edit Queries, add an index column for the basic table
Step2:
Close&Apply
Step3:
Add a calculate column by this formula
Result = IF ( AND ( Table6[Plant] = CALCULATE ( MAX ( Table6[Plant] ), FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 ) ), Table6[Product] = CALCULATE ( MAX ( Table6[Product] ), FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 ) ) ), IF ( Table6[move out date] > CALCULATE ( MAX ( Table6[move out date] ), FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 ) ), "Violation", BLANK () ), BLANK () )
Result:
here is pbix, please try it.
https://www.dropbox.com/s/mtpjwie1vanrt9i/Inventory%20first%20in%20first%20out.pbix?dl=0
Best Regards,
Lin
Hi @v-lili6-msft,
Sorry for the late reply, thank you for the brilliant way to tackle this problem!
It does help me a lot, on the task itself and as an idea for similar problems.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |