Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help creating Column by Phase and scan date

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!!!

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

4 Replies

  • dethompson97's avatar
    dethompson97
    Resolver III
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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

         

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.