Month over month using measure instead of column

[Anonymous]

I am trying to get a month over month increase over a year timeframe with a measure. I have seem videos where they use yearYTD function with calculate, but in all these circumstances they have a column that is connected to the date column. In my case, I cannot connect the relationship and I am using a measure instead of a column. 

The measure I have is terminations/active employees. This measure gives a percent per month for the year. It shows employee turnover per month. I also wanted to have a percent that grows throughout the year. So taking the begining of the year number and adding to it throughout the year. I am using userrelationships to connect the termination date with the active count. 
 

Let me know what more information I can provide. 

[Anonymous]

@danextian, I was wondering if you may know how to now do a running total of the numbers from that file? I noticed doing it from a measure instead of a column is much harder. Any ideas on that? 

[danextian]

Using the same data, can you please post a snapshot or your desired result?

[danextian]

Hi @Anonymous ,

Can you please post a sanitized sample pbix of your data model?

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2733012#M953338 

[Anonymous]

Here is an example of what I created. The turnover is correct. You will notice in the file that there is not an active relationship with the two tables. This needs to be the case in order to get the correct turnover percentage as its comparing the hire date and termination date. But in not having it connected, it makes it difficult to get a running total. I tried two different approaches from the file attached, but neither of them give me the correct results. it should show a total of previous months moving foward for the year or a timeframe filtered. 

 https://drive.google.com/file/d/1lkVybQZgvceugLA1oU1pzhZgRhCVWeuw/view?usp=sharing

[danextian]

If I understood correctly and using the data below, the total employee count if the selected date is 24 Feb 2021 is 4 and the turnover rate should be 0% as none of them has been terminated yet?

Also, why can't you create an active relationship but an inactive one is okay?

[Anonymous]

Hi Danextian, yes so you can do it manually as well to confirm the numbers, but you take the termination count for the month and then divide by the active count for that month to get the percentage. 

If you go in and activate the relationship, you will see the numbers change that will no longer match up with the numbers not in the relationship. 

[danextian · Accepted solution]

Hi @Anonymous 

If the screenshot below is your desired result for employee count, this can still be implemented using an active relationship between the dates table and hire date column.

You can use CROSSFILTER to disable any existing relationship within a measure.

Employee Count =
VAR selectedDate =
    MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUMX (
            data,
            VAR employeeStartDate = data[Hire Date ]
            VAR employeeEndDate = data[Termination Date]
            RETURN
                IF (
                    employeeStartDate <= selectedDate
                        && OR ( employeeEndDate >= selectedDate, employeeEndDate = BLANK () ),
                    1
                )
        ),
        CROSSFILTER ( 'Date Table'[Date], data[Hire Date ], NONE )
    )

or this approach sans the active relationship

Employee Count2 = 
VAR selectedDate =
    MAX ( 'Date Table'[Date] )
VAR result =
    CALCULATE (
        COUNTROWS ( data ),
        FILTER (
            FILTER ( ALL ( data ), data[Hire Date ] <= selectedDate ),
            data[Termination Date] >= selectedDate
                || data[Termination Date] = BLANK ()
        )
    )
RETURN
    result