10-18-2018 02:49 AM
Hi PowerBI People!
Some help needed please as I am going around in circles and reading so much on here I am losing the plot!
I have 2 data sources linked to one report - one showing all members who joined us over the past year, the other showing those who have left us over the year
I want to show number of joiners and the number of leavers in one line graph. Doesnt sound to difficult to me, but it simply wont play ball!
Now i appreciate this may be a quick fix for you lot, so feel free to point and laugh, but I cant see it for looking as my graph shows the monthly totals for one field [great!] but then the final total for the year for the other field [not so great!]
images show what i mean.
help. thank you. You are great!
10-18-2018 08:25 AM - edited 10-18-2018 08:26 AM
My guess is that you are confusing the visual by putting two unrelated timelines on the x-axis. (Lapsed Month and Date of Joining Month). It's only slicing the data by one of them and ignoring the other.
10-18-2018 08:46 AM
Create one Date dimension table.
link staff data on joining date with date table
link leavers data on leaving date with date table.
create 2 measures
joiners = countdistinct(staff table[emp id])
leavers = countdistinct(leavers[empl id])
Use 'Month' from Date table as axis and above 2 measure to plot the chart.
Hope this helps,
10-18-2018 08:02 PM
There is a simple way for you to refer to:
Use MONTH Function to create a Month column for these two data sources
Create a month fact table
Create the relationship between them by Month column
Drag the field month form Month table into Axis
here is pbix, please try it.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.