How to count totals of everything on or before a given date but show multiple months on one graph

[GunnerJ]

Given the code below I can see the current number of active accounts. I now need to display this number across a 12 month running total. Each account has a "connect date". The logic would need to count any account that has the same criteria already shown but also a connect date on or before each month displayed.

For example: If account#123 was active and had a connect date in FEB 2020 I would want it to contribute to the total in Feb and March of 2020 but not for Jan 2020 or any date before that. I'm currently unsure of how to add logic to a measure that is dependent upon a graph I use it in. Any help or suggestions would be greatly appreciated. I understand it may be confusing so please let me know if I can clarify anything. 

Master Sum Res = CALCULATE(COUNTROWS(DISTINCT('Master by Substation'[BI_ACCT])), 'Master by Substation'[BI_REV_CLASS_CD] = 1, or('Master by Substation'[BI_SRV_STAT_CD] = 1, 'Master by Substation'[BI_SRV_STAT_CD] = 18))

Please excuse my crude drawing but this is what the end result will hopefully look like. 

https://www.dropbox.com/s/ohaysgb6htq0ku1/OZARKSGO%20-%20SUBSCRIBER%20DASHBOARD.pbix?dl=0

This link shows you the file I'm working towards. It'll use the "Master by Substation" table. 

[dax]

Hi @GunnerJ , 

I am not clear about your requirement, I need to check the requirement with you. It seems that you want to calculate running total, you said that " If account#123 was active and had a connect date in FEB 2020 I would want it to contribute to the total in Feb and March of 2020 but not for Jan 2020 or any date before that. ", did you want to use slicer to control date range? And did  you just want to show selected month and later months instead of last 12 month of current selected month?

If this is not what you want, please correct me and explain more detailed logic to me.

Best Regards,
Zoe Zhi

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

[GunnerJ]

@dax 

Thank you for the reply.

I'm hoping for the measure to be dynamic in that it will adapt to however many months I have displayed it will show the running total for the given months. I have a date table setup and at first I would likely limit it to the last 12 months. If using a slicer would help I am fine with that. 

So for example as of today my chart would show April 2019 - March 2020 (current month). That way we can see growth over a year's time. 

In a small scale example if we had accounts with the following connect dates: Acct#1 Connect Date: Dec 12th 2019, Acct#2 Connect Date: Jan 8th 2020, Acct#3 Connect Date: Jan 21st 2020, Acct#4 Connect Date: Feb 2nd 2020, and Acct#5 connect Date: March 10th 2020.

The running totals would look like this: Dec 2019: 1 account, Jan 2020: 3 accounts, Feb 2020: 4 accounts, March 2020: 5 accounts. 

The graph would easily visualize that growth over time. 

I would like to build this logic on top of a previous measure that had the service statuses and revenue class criteria.

Master Sum Res = CALCULATE(COUNTROWS(DISTINCT('Master by Substation'[BI_ACCT])), 'Master by Substation'[BI_REV_CLASS_CD] = 1, or('Master by Substation'[BI_SRV_STAT_CD] = 1, 'Master by Substation'[BI_SRV_STAT_CD] = 18))

Does this help explain things?

[dax · Accepted solution]

Hi @GunnerJ , 

You could try below measure to see whether it work or not

Measure 2 = CALCULATE(COUNTROWS(DISTINCT('Master by Substation'[BI_ACCT])),FILTER(ALLSELECTED('Master by Substation'),or('Master by Substation'[BI_SRV_STAT_CD] = 1, 'Master by Substation'[BI_SRV_STAT_CD] = 18)&&'Master by Substation'[BI_CON_DT]<=MIN('Master by Substation'[BI_CON_DT]) && 'Master by Substation'[BI_CON_DT]>=DATE(YEAR(TODAY())-1,month(TODAY())+1,1)))

Best Regards,
Zoe Zhi

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

[GunnerJ]

@dax I tried the code but was not successful. We're going to try a different approach as it's a pretty unique challenge. Thank you for taking the time to look into the issue. 

[GunnerJ]

@dax was the above detail enough to help proceed with finding a solution? I'm curious as this is a rather confusing issue for me. Thanks