Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning!
I have two tables:
1)
LeaseNum | EffectiveDate | PriceContractNumber | Price |
436444 | 10/1/2020 | ENL-P07201052 | 0 |
436444 | 7/1/2020 | ENL-P07201052 | -3.80 |
2)
LeaseNum | TicketDate | Last Contract Date Lookup | Last Price Lookup |
436444 | 7/1/2020 | 7/1/2020 | -3.80 |
436444 | 8/1/2020 | 7/1/2020 | -3.80 |
436444 | 9/1/2020 | 7/1/2020 | -3.80 |
436444 | 10/1/2020 | 10/1/2020 | 0 |
I am trying to pull in data from table 1 into table 2.
I am not sure the best most efficent way to calculate this to get the results desired ( Red Columns).
I really appreciate any help, thanks all!
"Hope to be a datanaut"
Austin
Solved! Go to Solution.
maybe you can try to create two columns
Column = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]<=Table2[TicketDate]),Table1[EffectiveDate])
Column 2 = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]=Table2[Column]),'Table1'[Price])
Proud to be a Super User!
maybe you can try to create two columns
Column = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]<=Table2[TicketDate]),Table1[EffectiveDate])
Column 2 = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]=Table2[Column]),'Table1'[Price])
Proud to be a Super User!
Ok, thanks @ryan_mayu .
I'll leave you to it.
Pete
Proud to be a Datanaut!
I think this depends on how the DAX performs.
I'll have look now and see if I can put together something performant in DAX for you to test. If not, we'll do it in PQ.
Pete
Proud to be a Datanaut!
Hi @anorville ,
lol @ "Hope to be a Datanaut"!
*Edit* I didn't mean this in a mean way, I just thought it was a funny take on the "Proud to be a Datanaut" signatures 🙂
Are you aiming to do his in Power Query or DAX?
Pete
Proud to be a Datanaut!
Hey! @BA_Pete I am hoping to do this in DAX, unless you think Power Query would be best. Sorry for not specifying!
Austin
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |