Help creating Column by Phase and scan date

[Anonymous]

Hello everyone, 

Im needing help creating a formula that will look at a certain uploaded phase date by department #. Basically what i have is certain phases that were changed on a upload date. I am wanting to look at a units scan date by department and have it return what phase this unit was a part of. One sheet contains information as follows:

Department     Upload on       Phase 

ABC                   4/6/18               1

ABC                   5/9/19               2

ABC                   7/12/19             3

The other sheet contains the information of :

Department                 Scan Date

ABC                               9/5/18

ABC                               5/10/19

Im needing it to return a value of which Phase this product would have belonged to. Please help!!!

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create column Phase_matched to meet your demand.

Phase_matched =
MAXX (
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Department] = Table2[Department]
            && Table1[Upload on] <= Table2[Scan Date]
        ),
        Table1[Upload on], DESC
    ),
    Table1[Phase]
)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dethompson97]

I'm not sure that I understand the issue.  Can you please provide the desired output based on the input you have listed.

Thanks!

Dawn

[Anonymous]

Yes the top one would give me Phase 3 and the bottom Phase 2

[Anonymous]

So anything scanned from upload date on Phase 1 until Upload date on Phase 2 would be a Phase 1 product and so on