powerbi - Calculating Monthly Average over time with only Start and End Dates in Power BI

Question

In order to track our average headcounts and turnover trends, I had created a report in Power BI that essentially expanded a table with employee start/end information to give a record for each associate every day they were employed at the company.

While this solution works, I end up with a table containing millions of rows which does not seem very efficient.

I have looked into other possible solutions in DAX, but have not found anything that will give me the exact same output. It seems like most solutions rely on taking the timing from the start date and end date of a month and then averaging them, which is not quite what I am trying to do.

I have attached a simplified pbix file that shows the desired output of what I am trying to get at. Any advice would be appreciated.

pbix example

question from:https://stackoverflow.com/questions/65648703/calculating-monthly-average-over-time-with-only-start-and-end-dates-in-power-bi

Answer 1

Final code ended up looking like this:

All Employees = countrows ('Employees')

Terminations = 
Calculate(
    Countrows('Employees'),
    USERELATIONSHIP('Employees'[End Date],'Calendar'[Date])
)


Active Employees = 
AVERAGEX(
    VALUES('Calendar'[Date]),
    VAR CurrentDate = 'Calendar'[Date]
    VAR HiredBeforeCurrentDate =
        Filter(
            ALL('Employees'[Start Date]),
            'Employees'[Start Date] <= CurrentDate
        )
    VAR HiredAfterCurrentDate = 
        FILTER(
            ALL('Employees'[End Date]),
            OR('Employees'[End Date] >= CurrentDate, isblank('Employees'[End Date])
            )
        )
    RETURN
        CALCULATE(
            Countrows('Employees'),
            HiredBeforeCurrentDate,
            HiredAfterCurrentDate,
            ALL('Calendar')
        ))