Basic Data Modeling Question

[Anonymous]

I am fairly new to PBI, so forgive me if this is a simple question.

I have a data set that looks something like this: 

I am trying to create a basic line chart that shows the 'Month - Year' as the X Axis and counts both the number of new hires and terminations for that month on the Y Axis (2 different lines).  I also need to be able to create filters based on other data in the dataset such as Department, Manager etc. (not shown).  I've tried this several ways including separating out the termination data from the hire data, and linking them through a separate 'Month - Year' table which contains all unique values for month - year.  This works for showing the two lines in the visualization, but it won't allow me to filter by Manager, Department etc. correctly.  

If I keep all the data in one table it's not letting me link both the Hire Month - Year and the Termination Month - Year fields to the 'Month - Year' table.  So, it's the opposite problem - I can sort by Manager/Department just fine, but the data is not showing up correctly in the chart.

Any help would be much appreciated.

Thanks

[AkhilAshok · Accepted solution]

You can easily do this the following way:

1. Create a Date table with below logic and sort Year Month & Month based on the respective Number columns:

Date =
VAR BaseCalendar =
    CALENDAR (
        MIN ( Table[Hire Date], Table[Termination Date] ),
        MAX ( Table[Hire Date], Table[Termination Date] )
    )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
        RETURN
            ROW (
                "Year", YearDate,
                "Month Number", MonthNumber,
                "Month", FORMAT ( BaseDate, "mmmm" ),
                "Year Month Number", YearMonthNumber,
                "Year Month", FORMAT ( BaseDate, "m-yyyy" )
            )
    )

2. Create 2 Relationships from Date[Date] to Table[Hire Date] & Table[Termination Date]. One relationship will be automatically marked Inactive.

3. Create the following 2 measures (assuming Termination Date relationship is marked Inactive):

Hired Employee # =
DISCTINCTCOUNT(Table[Employee])

Terminated Employee # =
CALCULATE (
    DISTINCTCOUNT ( Table[Employee] ),
    USERELATIONSHIP ( Table[Termination Date], Date[Date] )
)

4. Create a line chart between Date[Year Month] & the 2 Measures.

Hope this helps.

[Anonymous]

This worked perfectly.  Thanks so much for the help!

[Anonymous]

Did you create a COUNT( ) measure for both Terminations and Hires? Seem like that is what you are missing. The chart will not count for you. Make the COUNT measures and bring those into the chart.

If the filters you said did not woirk in the chart work in the table then it's the measures. If the filters do not work in the table then you have a connection issue: the tables are not linked properly.  

[Anonymous]

Thanks for the response.

So, in the case you are suggesting I'm guessing I'd build Measures to count the Hires/Terminations by Month - Year.  I'm thinking I would need to create a measure for each unique Month - Year value...?  Is that right?  If so, what field do I use as the X Axis on the line chart?