Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ckennedy
Frequent Visitor

Mult Data Sources

Hello. I'd like to compare dates from two linked tables.

The first table is OPEN DATE with two columns: "Legal Name" and "Open Date"

The second table is DATE SET with two columns "Legal Name" and "Date Set"

 

The two tables are linked by "Legal Name". This works fine. I can bring Legal Name, Open Date and Date Set into a visualization.

 

I would like to calculate elapsed days by comparing the "Open Date" and "Date Set". Do I do this in the Query Editor? Or, do I add a custom column to OPEN DATE or DATE SET?

 

 

1 ACCEPTED SOLUTION

You can create a Merge Query in Power Query editor. All the way to the right in the ribbon. There are also functions like NATURALLEFTINNERJOIN in DAX that you could do this with.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

I would create a measure that does a subtraction of the MAX of the two dates or use DATEDIFF. This will work in a  context of Legal Name in a table.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
MagnusSthlm
Helper I
Helper I

Maybe you could merge the two tables, that would make it easier for you to create a measure that calculate the difference.

 

/Magnus

Thanks for the quick reply. I'm still getting the hang of Power BI at this point. How do you merge tables? Is this done through the query editor?

Hi!

 

Yes you merge in the Query Editor. Here's a video showing how to do it:

https://www.youtube.com/watch?v=A1OgxKZmbqg

You can create a Merge Query in Power Query editor. All the way to the right in the ribbon. There are also functions like NATURALLEFTINNERJOIN in DAX that you could do this with.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you! That's what I needed.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.