Calculate Resident Turnover from Invoice List

[Nighthawk12]

New Power BI user here 🙂 I've been able to get a lot of great answers from searching the forum so far but am stuck on a recent measure. The Power BI formula syntax in Power BI is not quite clicking for me yet. Here's my problem...

I have a list of "resident" invoices over a number of years in columns: invoice date, resident name, and amount. Residents can be invoiced weekly or monthly so their names may show up multiple times in a period. I used DistinctCount to get a graph of total residents by month over time. I would like to get an idea of turnover and be able to count any residents added or lost. All I have are invoice records so my formula needs to follow the logic:

New Residents = Current Month Residents Invoiced - Prior Month Residents Invoiced

Lost Residents = Current Month Residents Invoiced - Next Month Residents Invoiced

I could do it pretty quick in Excel but I'm struggling with the syntax in Power Bi

[Anonymous]

Hi @Nighthawk12 ,

Maybe you can try formula like below to create measure:

New Residents =
VAR CurrentMonthResidents =
    DISTINCTCOUNT ( 'Table'[Resident Name] )
VAR PriorMonthResidents =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Resident Name] ),
        PREVIOUSMONTH ( 'Date'[Date] )
    )
RETURN
    IF (
        ISBLANK ( PriorMonthResidents ),
        CurrentMonthResidents,
        CurrentMonthResidents - PriorMonthResidents
    )

Lost Residents =
VAR CurrentMonthResidents =
    DISTINCTCOUNT ( 'Table'[Resident Name] )
VAR NextMonthResidents =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Resident Name] ),
        NEXTMONTH ( 'Date'[Date] )
    )
RETURN
    IF (
        ISBLANK ( NextMonthResidents ),
        0,
        CurrentMonthResidents - NextMonthResidents
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nighthawk12]

@Anonymous Thanks for the response! This appears to be almost what I need! I may be misreading it but it looks like each variable is a count and then the difference between the counts is returned. Wouldn't this just give me the net change?

For example, what if I added 2 new residents but lost 2 residents in the same month? I would like to see both the residents added and the residents lost as two separate numbers even though the total resident count has not changed.

[Nighthawk12 · Accepted solution]

@Anonymous Thanks again for the help! I was able to modify it to get what I needed. See below:

[Anonymous]

Hi @Nighthawk12 ,

Thanks for your feedback.

Best regards,

Adamk Kong

[Nighthawk12]

To clarify... I need to filter one list of names by another and then count the result