Summarise totals based on contract startdate, enddate

[rbovenkamp]

I have a csv file with 3 columns:

Customer name, contract start date, contract end date
Rick, 23-01-2018, 25-02-2018
Tom, 15-01-2019, 18-03-2021
Maarten, 15-08-2019, 19-01-2022

End so on.

I want to visualise the active customers in a chart. Whatever I try, it always gave me the active customers per day and not the summarized totals. So if I have 230 active customers today and by the end of the day 5 customers ends their contracts, it should show me 225 active customers in total by tomorrow.

Any help would be highly appreciated. I drives me a little crazy as I’m searching for an answer for almost a week now 🙂

[lbendlin]

Do you have a Dates table in your data model? Any relationships defined?

[rbovenkamp]

Yes, I have both a date table and a relationship between the customer table.

[lbendlin]

let's assume you have the Dates[Date] field as your first column / on your X axis

Now for each of the selected dates you want to calculate the number of contracts where the start date is lower and the end date is higher than the date. "Selected"  here means the visible part of your visual.

A measure could look like this (pseudo code)

Active contracts = 

var d = selectedvalue(Dates[Date])

return CountX(Contracts,Filter(Contracts,Startdate <=d && Enddate >d))

[rbovenkamp]

Unfortunately it returns an error when trying your code snippet. The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. 

Right now I’m using this measure:

Active Customers = 

var varToday = TODAY() 

return 

CALCULATE(COUNT(‘Active Customers’[Client]), Filter(‘Active Customers’,varToday > ’Active Customers’[Startdate] && varToday < ‘Active Customers’[Enddate]))

This almost looks the same as your code snippet. But unfortunately with this DAX code it's output is new customers on a per day base and not cumulative. Is it the COUNTX which returns a cumulative values for the chart? I try to learn it, could you explain the difference? I think I should seek it in a different direction, could it be somewhere in the relationship properties between date table and customer table? Probably in the DAX code used to create the date table? It seems very unlogical to me at this moment. 

Hope you can help me out. Thanks!

[lbendlin]

Note that I didn't ask about the relationship details. I tried to give you a formula that runs without needing one, purely based on the date column in your calendar table. 

I'm not at a Power BI desktop at the moment, will validate my pseudo code later.

Yes, the functions ending in -X usually do a computation across the entire table, not just the "selected" "row"  (my quotes).

[lbendlin]

This works without a table relationship

Active Contracts = 
var d = SELECTEDVALUE(Cal[Date])
var sm = countrows(filter('Table (5)','Table (5)'[Contract Start Date]<=d && 'Table (5)'[Contract End Date]>d ))
return sm

You can choose to "show items with no data"  for your Cal[Date]  column to also show dates where no contract was active.