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.
This has got me scratching my head, and as much as I see many earliest date type solutions, I'm not able to apply any of them to my situation.
Here is the link to an image of my sample dataset on imgur (I can't figure out how to upload photos!?): https://imgur.com/rcTCrjA
I have two tables
I can link the two tables by Site ID, however, I need to see SKUs by which Plan Type they are intended for. I want to select the plan type at the site where the construction start date is the next earliest date after the posting date of the SKU as I'm fairly confident this will give me a roughly accurate estimate.
Solved! Go to Solution.
Hi @boot_heel ,
You can create column Plan Type_matched to meet your demand.
Plan Type_matched= MAXX(TOPN(1,FILTER(Table2,Table2[Site ID]=Table1[Site ID]&& Table2[Cons.Start Date]>=Table1[Posting Date]),Table2[Cons.Start Date],DESC),Table2[Plan Type])
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @boot_heel ,
You can create column Plan Type_matched to meet your demand.
Plan Type_matched= MAXX(TOPN(1,FILTER(Table2,Table2[Site ID]=Table1[Site ID]&& Table2[Cons.Start Date]>=Table1[Posting Date]),Table2[Cons.Start Date],DESC),Table2[Plan Type])
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
There need not be a relationship between the 2 Tables. Write this calculated column formula in Table1
=LOOKUPVALUE(VALUES(Table2[Plan Type]),Table2[Cons. Start Date],CALCULATE(MIN(Table2[Cons. Start Date]),FILTER(Table2,Table2[Site ID]=EARLIER(Table1[Site ID])&&Table2[Cons. Start Date]>EARLIER(Table1[Posting Date]))),Table2[Site ID],Table1[Site ID])
If you face a problem with my formula, then share the link from where i can download your PBI file.
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |