Count of Active Companies Help

[donovanbush]

Hi everyone, 

I'm new(ish) to Power BI and I'm trying to create a report for my company to look at how many Active companies we add by month. I have a table that I'm pulling in from an OData feed with the system we are working with. Here's an example that went wrong when doing a simple count of Active CompanyStatus (there is another column for the company ID, joined with a master company table):

NSF is still technically an active company status, so when the field is switched back to Active on 4/18/2023, it counts that as a new addition in both March (from the 3/3/2022 entry) and April (from 4/18/2023). Is there a way I can make it only count once, unless there is a Termination status (i.e. Terminated/TermwithAccess in my table) in between the two Active statuses, in that instance it should be counted twice. 

Thank you so much for the help! I can provide more info as needed.

-Don

[amitchandak]

@donovanbush , two measures like these

Max Status = calculate(lastnonblankvalue(Table[Effective Date], max(Table[CompanyStatus])), filter(allselected(Table) , Table[Company] = max(Table[Company]) && Table[Effective Date] <= max(Table[Effective Date]))

Active Company = countx( filter(Values(Table[Company]), [Max Status] in {"Active"}), Table[Company])

[donovanbush]

@amitchandak Thank you!

So, when I use those two measures and check the work by outputting a table, this is what I get: 

But in my table it appears like this: 

So in this instance, only the 3/3/2022 date should be the real Active date, because the second Active date comes after an Active status (NSF or Inactive are both still Active). When I visualize the data, I'm still getting too high of a count because it's double counting these. Now, if the NSF was a Terminated status (Terminated or TermwithAccess), the Active date both before and after because they are two separate additions. 

Essentially, if there's a Termination for a company, and two Active statuses (one before and one after), we want both of those Active status dates, but if there's no Termination statuse and two (or more) Active statuses, we want only one, the earliest one. 

I apologize if I'm not being clear enough, this problem seems a little abstract for me with my understanding of Power BI. I hope this helps! 

-Don