Calculating active customers over time based on start and end date

[ErikHolmberg]

Hi,

I'm quite new to Power BI and I'm trying to do a fairly simple calculation(I think) but I can't get it quite right.

A want to calculate number of active customers over time based on their start date and end date. The table I'm working from includes different services, I want to do the calculation for only one specific service.

I've googled and followed a bunch of different methods but none get it right for me. I know the the correct numbers, done the calculations in excel to sainty check the output I'm getting in the quieries, but can't get it into Power BI

This is my current query 

Active Services =
VAR CurrentDate = MAX(DateTable[Date])
VAR ActiveServices =
CALCULATE(
COUNTROWS(servicelist),
ALL(DateTable),
DateTable[Date] <= CurrentDate,
ISBLANK(servicelist[End Date])
|| servicelist[End Date] >= CurrentDate,
servicelist[Product] = "Service"
)
RETURN
ActiveServices

If anyone can help my understand what and why it's not working correcty, please give me your thoughts

Thanks

[ErikHolmberg · Accepted solution]

I followed this guide and got it to work HR Analytics - Active Employee, Hire and Terminati... - Microsoft Fabric Community

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Thanks for the reply from KartheekJ.

Hi @ErikHolmberg ,

I created simple test data:

Do not create a relationship between two tables:

Modifications to the original formula:

New Active Services = 
VAR CurrentDate = MAX(DateTable[Date])
VAR ActiveServices =
CALCULATE(
COUNTROWS(servicelist),
'servicelist'[Start Date] <= CurrentDate,
ISBLANK(servicelist[End Date])
|| servicelist[End Date] >= CurrentDate,
servicelist[Product] = "Service"
)
RETURN
ActiveServices

Result:

Best Regards,
Zhu

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

[ErikHolmberg]

Really appriciate the answer and this is great!

What I can't seem to get from this is a visual of the acumulated number of customers on that service like

2024-02-15    1
2024-03-01    2

2024-04-01    3

2024-05-01   4

etc

Do you have any suggestion how to get there?

[Anonymous]

Hi @ErikHolmberg  , 

Please create another measure on top of the original, with the following formula:

New Active Services Result = IF([New Active Services]<>BLANK(),SUMX(FILTER(ALL('servicelist'),'servicelist'[Start Date]<=SELECTEDVALUE('servicelist'[Start Date])),[New Active Services]))

Result: 

Best Regards,
Zhu
Community Support Team

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

[KartheekJ]

@ErikHolmberg 

The key here is not to give a relation between the Customers table and Date Table.

Please go through the below link to understand the logic.
https://community.fabric.microsoft.com/t5/Community-Blog/Dynamic-Headcount-Analysis-using-Dax/ba-p/4... 

[ErikHolmberg]

Thanks @KartheekJ, I'll check it out