Cumulative Sum considering start and end dates

[GunnerJ]

I'm trying to show the growth of our customer base and can pretty easily get the cumulative count based on connect date but realized that it's showing the all time connection and not who was active at any point.

This is the original code. I have a join from my customer table "Elec Service All Time" to my date table using customer connect date.

Cumulative Accts = 
    CALCULATE(
        COUNT('Elec Service All Time'[BI_ACCT]),
            'Date Table'[Date] <= MAX('Date Table'[Date])
    )

I need to add a condition where not only is the connect date before the listed date but the disconnect date is after the listed date.

I tried to add something along the lines of this

&& BI_DISC_DT > MAX('Date Table'[Date])

 but it won't even allow BI_DISC_DT to show up when typing.

I'm sure the edit to the code is simple but it's escaping me at the moment. To recap I need to know active customers by date where an active customer is someone who's connect date (BI_CON_DT) is on or before the date and who's disconnect date (BI_DISC_DT) is after the date. If someone is currently active as of TODAY their disconnect date is set to '12/12/3000'. I'm hoping to display this on a graph to show a stready rise in customers. The end result for today should be around 89-90k.

Thank you and I'm happy to provide additional info if needed. 

Link to sample file

https://www.dropbox.com/scl/fi/9o2plu4tit0f7r9k5h05z/Customer-Count-Test.pbix?rlkey=nr7mjbin0ken2nfy...

[Greg_Deckler]

@GunnerJ See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[GunnerJ]

@Greg_Deckler 
Thank you for the quick reply. 
I'm able to get the "connects" to work using your logic. However, I'm still stuck on the disconnect piece.

The first part which is currently commented out works to show adds per day in a cumulative sum.

I was playing around with having a disconnect version and subtracting it from the connects but without success.

*I'd tried switching the "max_date" value to connect date to stay the same *

Cumulative Accts = 
/*
var max_date = MAX('Elec Service All Time'[BI_CON_DT])
var table_A = FILTER(ALLSELECTED('Elec Service All Time'), 'Elec Service All Time'[BI_CON_DT] <= max_date)
   return  COUNTX(table_A, 'Elec Service All Time'[BI_ACCT]) */

var max_date = MAX('Elec Service All Time'[BI_DISC_DT])
var table_B = FILTER(ALLSELECTED('Elec Service All Time'), 'Elec Service All Time'[BI_DISC_DT] <= max_date)
return COUNTX(table_B, 'Elec Service All Time'[BI_ACCT])

Any advice as to how I could best incorporate the subtraction of disconnected accounts?

[Anonymous · Accepted solution]

Hi @GunnerJ ,

Check if this is your expected output:

Cumulative Accts = 
VAR max_date = MAX('Date Table'[Date])
RETURN
    CALCULATE(
        COUNT('Elec Service All Time'[BI_ACCT]),
            'Elec Service All Time'[BI_CON_DT] <= max_date
            && 'Elec Service All Time'[BI_DISC_DT] > max_date
    )

Best Regards,
Gao
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.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Joselete]

Hi,

thanks for the explanation, but I am also trying the same, using the max and min output of this slide in order to filter a table with cumulative counting. 

The problem is, that it only works with the max_date as higher limit but not with the min_date as lower limit from the referenced Datetable

Here when I try with 

Append1[Ist_Stx56]<=max_date&&Append1[Ist_Stx56]>=min_date

so the table is always filtered with the output dates from a visual

as you can see it does not sum accumulative, just counts.

But if I change for example the min_value for an specific date (first january for example isntead of an output from the date visual), it works the cumulative sum:

here with specific date as minimum: