cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-xicai
Community Support
Community Support

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.

View solution in original post

dethompson97
Resolver III
Resolver III

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
Not applicable

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

Anonymous
Not applicable

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors