Dates Between Dates Diff Tables

[Anonymous]

Hi, 

Is there a way to compare dates between two tables, and say 

if "TRANS_DT" is less than "Effective Date" in one row, but greater than "Effective Date" in the previous row, use "Grade" for that "RANS_DT" 

So for the screenshots below, since the "TRANS_DT" for the first row is 12/29/2019, and the "Effective Date" in row 3 of the second screenshot is 12/19/2019, row 4 is 6/1/2020, the row output for the formula would be "3"...

          

Please let me know if this question does not make sense...

Thank you!
Sarah

[amitchandak]

@Anonymous , not very clear. But refer my blog on the same topic

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

Hm, I am not trying to find the date difference, but to see whether TRANS_DT is between two dates in a column in a different table.

If so, I am trying to put the GRADE from the row of the Effective Date  that is before the TRANS_DT

[Anonymous]

Basically, can you do an index match formula in Power BI i think is my question...

=INDEX('Job Title'!$H$2:$H$6,MATCH(1,IF('Job Title'!$C$2:$C$6<=Labor!M4,1)))

[amitchandak]

@Anonymous , Refer if this can help. https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Take the idea of how current employees are created

Check this file has a better example - https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Check how the leave between dates summed up

Or

Can you share sample data and sample output.

[Anonymous]

https://docs.google.com/spreadsheets/d/1cNTBijTIKX2PRo991UALOsxtyMV3JsxJ0CDF5RJYsjE/edit?usp=sharing

@amitchandak 

i am not sure how to share an excel file so i created it in google docs... hope you can see it haha.. my goal is to get the GRADE column in the Labor tab through a Power BI formula, if possible

Thank you!

Sarah

[amitchandak · Accepted solution]

@Anonymous , Check the attached file after signature

[Anonymous]

@amitchandak you are the best!!!!