Sum of values between start/end date, where some end dates are 'null'

[chrisb182]

Good morning,

I am trying to sum values for various accounts which all have a start date, but only some have a terminated date.

For example, if I select my slicer to view sales for months January - May, I would expect:

- For an active account that has a start date of 01/02/2024 to sum their sales for February, March, April and May,

- For a terminated account that started on 01/03/24 but terminated on 30/04/24, to sum their sales for March and April only.

I have tried to use the DATESBETWEEN function (using 'Start Date' and 'Terminated Date') in conjunction with a SUM/CALCULATE formula, but the DAX keeps failing because (obviously) the live accounts don't have a date in the Terminated Date.

Does anyone know of a simple DAX that I can use? 

I hope that the above makes sense.

Cheers,

Chris

[chrisb182]

Hi @DataNinja777 

I cant get that formula to work unfortunately.

Here's some sample data as requested.

Account	SubGroup Start Date	SubGroup Terminated Date	Sales Date	Sale Amt
L	12/01/2023	08/02/2023	15/04/2023	3,718
W	06/02/2023		15/04/2023	9,152
C	30/04/2023	10/12/2023	17/04/2023	777
X	07/04/2023	22/03/2024	28/04/2023	2,012
J	27/02/2023		05/05/2023	5,430
F	12/03/2023	23/01/2024	06/05/2023	5,940
J	27/02/2023		10/05/2023	9,783
S	27/03/2023		12/05/2023	2,273
S	27/03/2023		14/05/2023	7,152
Y	15/04/2023	24/02/2024	21/05/2023	6,752
R	29/03/2023		04/06/2023	4,296
X	07/04/2023	22/03/2024	06/06/2023	6,819
A	30/12/2023		12/06/2023	541
W	06/02/2023		12/06/2023	6,756
U	07/01/2023		16/06/2023	3,463
F	12/03/2023	23/01/2024	19/06/2023	7,023
V	26/04/2023		22/06/2023	7,337
M	13/04/2023		29/06/2023	2,104
X	07/04/2023	22/03/2024	02/07/2023	8,680
Z	02/03/2023	25/01/2024	08/07/2023	82
H	10/02/2023		11/07/2023	9,676
I	05/04/2023	06/01/2024	11/07/2023	4,958
E	21/01/2023		12/07/2023	1,833
Y	15/04/2023	24/02/2024	14/07/2023	5,116
C	30/04/2023	10/12/2023	24/07/2023	4,137
Z	02/03/2023	25/01/2024	25/07/2023	2,712
E	21/01/2023		27/07/2023	5,509
M	13/04/2023		27/07/2023	8,573
D	31/01/2023	31/01/2024	31/07/2023	3,964
B	27/03/2023		01/02/2024	3,588
W	06/02/2023		03/02/2024	1,173
D	31/01/2023	31/01/2024	12/02/2024	6,670
K	28/04/2023	17/11/2023	13/02/2024	1,518
R	29/03/2023		14/02/2024	8,942
U	07/01/2023		14/02/2024	9,294
N	06/04/2023		17/02/2024	6,753
Y	15/04/2023	24/02/2024	18/02/2024	3,394
D	31/01/2023	31/01/2024	19/02/2024	1,671
E	21/01/2023		21/02/2024	5,863
R	29/03/2023		24/02/2024	5,568
K	28/04/2023	17/11/2023	25/02/2024	8,383
E	21/01/2023		27/02/2024	2,099
L	12/01/2023	08/02/2023	29/02/2024	8,260
E	21/01/2023		01/03/2024	8,966
B	27/03/2023		02/03/2024	2,393
F	12/03/2023	23/01/2024	02/03/2024	2,586
O	28/02/2023	02/05/2023	02/03/2024	2,844
Y	15/04/2023	24/02/2024	09/03/2024	8,280
F	12/03/2023	23/01/2024	14/03/2024	8,944
N	06/04/2023		14/03/2024	3,972
R	29/03/2023		16/03/2024	3,403
G	20/03/2023	07/05/2023	23/03/2024	2,890
K	28/04/2023	17/11/2023	23/03/2024	6,208
L	12/01/2023	08/02/2023	25/03/2024	5,209
X	07/04/2023	22/03/2024	25/03/2024	9,651
D	31/01/2023	31/01/2024	26/03/2024	9,970
G	20/03/2023	07/05/2023	07/04/2024	614
G	20/03/2023	07/05/2023	07/04/2024	5,448
J	27/02/2023		12/04/2024	257
X	07/04/2023	22/03/2024	14/04/2024	5,333
V	26/04/2023		15/04/2024	6,310
J	27/02/2023		16/04/2024	7,539
O	28/02/2023	02/05/2023	16/04/2024	2,331
S	27/03/2023		17/04/2024	3,735
V	26/04/2023		17/04/2024	5,748
C	30/04/2023	10/12/2023	18/04/2024	7,276
Y	15/04/2023	24/02/2024	18/04/2024	4,273
B	27/03/2023		29/04/2024	4,575
T	17/03/2023		29/04/2024	5,980
R	29/03/2023		05/05/2024	7,724
J	27/02/2023		08/05/2024	9,736
T	17/03/2023		10/05/2024	2,879
N	06/04/2023		13/05/2024	1,477
Z	02/03/2023	25/01/2024	13/05/2024	7,068
E	21/01/2023		16/05/2024	9,448
U	07/01/2023		19/05/2024	2,905
A	30/12/2023		22/05/2024	9,110
P	27/01/2023		22/05/2024	2,950
S	27/03/2023		25/05/2024	8,761
Q	03/04/2023		26/05/2024	9,374
C	30/04/2023	10/12/2023	29/05/2024	5,444

[DataNinja777 · Accepted solution]

Hi @chrisb182 

Thank you for providing me with the workable data. There are mutiple ways to produce your required output and one of them is as follows.  I've written two dax formulas as shown below, one indicating the number of the open accounts at any given point in time (duration of counts), and another indicating the sum of the sales during the period accounts are open.  

The first measure expressing the count of accounts during the open period is as follows:

The second measure shows the sales amount during the period account is open.  For this, nstead of the 2 conditions, the 3rd condition of the revenue recording date is within the account opening period is added to the 1st measure, and the sales amount, instead of the count of the account was sumxed over the table.

The important thing for this data model to work as intended is to have the calendar table as a disconnected table from the fact table as shown in the data model below.  This is because there are multiple dates in the fact table, we should not relate the date fields to the calendar table for the data model to behave in the way we want with respect to the time dimension.  

The resultant visualization of the two measures above is as follows:

Please let me know if the above meets your expectations.  I attach an example pbix file below.

Best regards,

[DataNinja777]

Hi @chrisb182 ,

One general question which I have from reading your requirement above is how can a terminated account have sales after the termination, and also, how can an account not yet open have a sales, and from this reason of tautology, I thought whether it is open or not is a superfluous condition for adding up sales becuase all the accounts which have sales cannot be other than open account (not yet closed).  Is this assumption correct?  If not, please let me know in which cases closed accounts can have sales amounts in your specific business circumstances.  Regarding the accounts which are not yet closed and have blank values in the closed date field, I recommend to create another calculated column where closed dates are assumed to be like certain dates in the future like 6 months later, so that the calculation of the duration will show it as open as of the analysis dates by writing if formula, such as, if closed date is blank() then today()+30*6, otherwise, closed date.  Sorry for not using exact dax formula and writing in words, but I am assuming that you know what I meant.  

Best regards,

[chrisb182]

Hi @DataNinja777 

Thank you for your response.

Basically, the data I source has numerous accounts. Some of these accounts can join a separate group of accounts at any given time (lets call this SubGroup), so a start date is given to those accounts. Simarily, if those accounts want to opt-out of the SubGroup they are given a terminated date.

The problem with the source data is that I cannot just pull data for those accounts in the SubGroup - it has to be for all accounts.

For example, if I source data for the whole of 2023 there will be 150 out of 2000 accounts that joined this SubGroup at any given time during 2023, and I just need to collate their sales whilst part of this SubGroup during 2023.

That's probably as clear as mud, but I hope it makes some form of sense.

[DataNinja777]

Hi @chrisb182 ,

Thanks for your explanation of your specific business circumstances.  I got the point.  In that case, what about writing a formula like below?  

The key to getting the formula above to work is to have your calculated Calendar table as a disconnected table from your sales fact table.  

If I can have more sample field names and dummy data, I can prepare pbix file, but hopefully you can get started with the above.  

Best regards,