Show measures by different dates on same timeline

[aashton]

Hello,

I have a Leads table, which links to an Applications table, many to one.  Leads has date, name, email, etc.  Applications has Date applied, name, email etc.  I am trying to figure out how to display a column chart by month, total Leads and total Applications.  But, I want to show total Leads by the month of the lead date and total Applications by the month of the Applied date. For example, in January we had 50 leads (so display 50 on January), and out of these, 10 applied in Feb, 40 in March so those Applications need to be under Feb and March.  Since these 2 tables have a relationship, I can't figure out how to display them by different dates??  Right now I just have Lead Month and Year on the X axis, Total Leads and Total Applications on the Y, so it is displaying the Applications by the month the lead for that applicaiton came in, not the actual month they applied.  

[JoeBarry]

Hi @aashton 

You need a Date Table that has relationship on the Date column of both Applications and Leads table on their date column. Remove or deactivate the current relationship between them both.

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

Create a the Bar chart. Ad´d the Year and month columns from the date table in the axis and then add the value columns from both tables in the value field. this will show you then what you need.

Thanks

Joe

If this post helps, then please Accept it as the solution

[aashton]

@JoeBarry  Thank you so much for your help.  I thought I had to do something like this, but i need the relationship between Leads and Applications, joined on email, to only see the applications for those people.  I only want to show applications for the Lead entries (there are many more applications for people not in the Leads table), so how would I accomplish this?

[JoeBarry]

Create a table with all of the distinct email addresses and create a one to many relationships between both tables. 

read up on the star schema, factvtables shouldn't have relationships with each other.