Counting and substracting by date

[JPux]

Hi all,

Tx in advance for reading this. For sure a very basic question for most of you, but I've searched the forum for similar entries and I couldn't find what I needed. Apologies in advance if I missed it. I'd appreciate your guidance with the following:

What I need to do: Say a company launches a product. I need to build a dashboard with 4 charts showing:

1. the total new customers that joined each month (aka NCA)
2. the total number of customers lost each month (aka Churn. Customers that do not renew their monthly subscription)
3. the net total (NCA - Churn) of each month
4. the total number of customers since the beginning to that month (running total)

I've done #1 and #2, (pbix file here), but I don't know how to do #3 and #4.

Source of data: File with sample data (this table shows the first 14 rows)

Customer	Country	NCA Date	Churn Date
John Doe 1	3	01/09/2023
John Doe 2	2	01/09/2023
John Doe 3	2	01/09/2023
John Doe 4	1	01/09/2023
John Doe 5	1	17/10/2023
John Doe 6	2	17/10/2023
John Doe 7	2	17/10/2023	16/12/2023
John Doe 8	3	17/10/2023
John Doe 9	1	17/10/2023	16/11/2023
John Doe 10	2	17/11/2023	16/12/2023
John Doe 11	2	17/11/2023	17/12/2023
John Doe 12	2	17/11/2023	06/02/2024
John Doe 13	2	17/11/2023
John Doe 14	3	08/12/2023
...

- "NCA Date": the date (dd/mm/yyyy) when the customer joined

- "Churn Date": the date when the customer's subscrition will be cancelled.

Rationale:

* Sep 2023: the product is launched. 4 new customers join (JohnDoe1 to JohnDoe4). No customers leave.

NCA: 4 <= (JD1 to JD4)

Churn: 0

NCA-Churn: 4

Total: 4

* Oct 2023: 5 new customers join (JohnDoe5 to JohnDoe9). After some days JD9 says he won't renew in Nov.

NCA: 5 <= (JD5 to JD9)

Churn: 0

NCA-Churn: 5

Total: 9

* Nov 2023: 4 new customers join (JohnDoe10 to JohnDoe12). After some days JD7, JD10 and JD11 say they won't renew in Dec.

NCA: 4 <= (JD10 to JD12)

Churn: 1 <= (JD9)

NCA-Churn: 3

Total: 12

* Expected results:

First graph:

Second graph:

Third graph:

Fourth graph:

👉Questions / Doubts where I'd appreciate your help:

1. How do I build the "NCA - Churn" graph?

2. How do I build the running total graph?

3. How can I force a zero for the months where the value of "Total NCA" or "Total Churn" is null? (i.e Churn for Jan 2024 is zero, but my visual simply connects Dec 2023 with Feb 2024 because in the source data that value is empty)

4. The range of the X axis must be the same for the 4 graphs, starting on the first date of NCA, and ending on the last date of the known Churn. This is to ensure vertical aligment when using the dashboard. I've done this by creating this new table:

CalendarMaster = CALENDAR(MIN('DataSourceTest'[NCA Date]),MAX('DataSourceTest'[Churn Date]))

and then using it to format the range limits of the X-axis in "Visualizations\Visual\X-Axis\Range". It works, but it'd be great if you could confirm whether this is the correct way to do it.

I thinks that's all. 🙈

Any guidance will still be really appreciated.🤗

Thanks a million,

J

[v-yaningy-msft]

Hi, @JPux 

I think you should rethink the logic of implementing the third and fourth images. For example, if the X-axis of NCA is based on NCA Date and the X-axis of Churn is based on Churn Date, then it is not clear which column the X-axis of the third image is based on; on the image it looks like the row values of both are directly subtracted from each other, but the columns that the X-axis is based on are not the same, and a direct subtraction won't give you the answer that you are looking for. The image-based representation is a bit abstract, so I don't know if you'll get the point. I will also try to find a solution to help you solve the problem as soon as possible.

Best Regards,
Yang
Community Support Team

[JPux]

Hi Ynag (@v-yaningy-msft), tx for your reply.

I've edited my initial post and added the rationale for the calculations. Hope it brings more clarity.

Thanks a lot for your help.

J

[v-yaningy-msft · Accepted solution]

Hi, @JPux 

You can try following DAX to solve the problem, however, the fourth Total graph needs to be used in conjunction with the NCA - Churn chart, which you can use at your discretion.

Here are the steps to make a Total line chart.
Step 1:
Right clock the third NCA - Churn chart, if you have not the New calculation, you can open Visual calculation in Preview features in Options and Settings, and then reopen the pbix file.

Step 2:
Right clock the third NCA - Churn chart, and choose New calculation, clock Fx and choose Running sum, then replace the Filed with NCA - Churn.

Best Regards,
Yang
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

[JPux]

Hi Yang ( @v-yaningy-msft )

Thanks so much for your reply.

Seeing how you created the "NCA-Churn" chart, has helped me to clarify some concepts I had misunderstood.

And I figured out how to do the Running Total in a separate chart. FYI.

(File with Dashboard v5)

- I've created two new columns in the main "DataSourceTest" table, as follows:

Churn YYYYMM = (YEAR([Churn Date])*100)+ MONTH([Churn Date])

NCA YYYYMM = (YEAR([NCA Date])*100)+ MONTH([NCA Date])

- In the "CalendarMaster" table, I've created another column following this same logic:

MasterDate YYYYMM = (YEAR([Date])*100)+ MONTH([Date])

- And then I've made this meassure for the Running Total:

CustomersRunningTotal = 
VAR _Month = SELECTEDVALUE('CalendarMaster'[MasterDate MM_YYYY])
VAR _MonthNum = YEAR(_Month)*100 + MONTH(_Month)
VAR _TodayNum = YEAR(TODAY())*100 + MONTH(TODAY())

VAR _NCAtoDate = CALCULATE(COUNTROWS('DataSourceTest'),'DataSourceTest'[NCA YYYYMM]<=_MonthNum)
VAR _ChurntoDate = CALCULATE(COUNTROWS('DataSourceTest'), 'DataSourceTest'[Churn YYYYMM]<=_MonthNum && 'DataSourceTest'[Churn YYYYMM]<> BLANK())

VAR _RunningTotal = if(_MonthNum <= _TodayNum, _NCAtoDate - _ChurntoDate,BLANK())
RETURN _RunningTotal

(The _TodayNum is to stop the graph from displaying values beyond the current month.)

With all the above, building the visual is quite easy:

I mark your post as the "Accepted Solution" because I've learnt more by analyzing the pbix file in your post above than with dozens of articles.

Thanks a lot for your help.

J