cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Column totals different from the grand total

Hey guys,

I'm computing the 30 days churn rate and I've came across a calculation problem. Basically the total number is correct (I've checked it myself), however the columns numbers are not being correctly computed. The calculation is the following:

``````Churn =
VAR _NDaysCustomers =
CALCULATETABLE (
VALUES ( Fact[User] ),
DATEADD ( d_Calendar[Date], -30, DAY ),
USERELATIONSHIP ( d_Calendar[Date], Fact[End] )
)
VAR _IntervalCustomers =
CALCULATETABLE (
VALUES ( Fact[User] ),
DATESINPERIOD ( d_Calendar[Date], MIN ( d_Calendar[Date] ), -30, DAY )
)
VAR _Except =
COUNTROWS ( EXCEPT ( _NDaysCustomers, _intervalCustomers ) )
RETURN _Except``````

The following image shows what I got from the above calculation and what is the actually result, where the former is shown above and the latter being the bottom table.

There are slights differences, but these make the numbers wrong.

Any thoughts on where I'm doing my calculation wrong?

14 REPLIES 14
Super User

Hi,

It will help if you first explain the question in simple language, share some sample data (share only as many tables and columns within those tables as are required to solve this problem) and show the expected result based off the sample data table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

The very second line contains the file link, and also there's an image, a description of my problem and also the formula.

Super User

What do the payment, start and end columns represent?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

The payment column stands for the payment method selected/used and the start and end columns represent the dates in which the subscription (or the plan) was active

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

Solution Sage

Probably using-30 in one variable and -31 in another variable is causing the difference

Resolver I

I've tried using both arguments as -30 and event then it didn't work. I posted it here with the difference you spoted, but i did not mean to. It was a mistake of my part.

Thanks for noticing

Solution Sage

The other possibilities  I can think of are using DATEADD in both or DATESINPERIOD in BOTH or

in the second variable, instead of MIN(d_CalendarDate), you may try  MAX(d_CalendarDate)

Resolver I

Hey buddy, could you help me out with this topic, please?

Super User

Hello @Pedro503,

Could you please try the following:

``````Churn =
VAR _NDaysCustomers =
CALCULATETABLE (
VALUES ( Fact[User] ),
DATEADD ( d_Calendar[Date], -30, DAY ),
USERELATIONSHIP ( d_Calendar[Date], Fact[End] )
)
VAR _IntervalCustomers =
CALCULATETABLE (
VALUES ( Fact[User] ),
DATESINPERIOD ( d_Calendar[Date], MIN ( d_Calendar[Date] ), -31, DAY )
)
VAR _Except =
COUNTROWS ( EXCEPT ( _NDaysCustomers, _IntervalCustomers ) )
RETURN _Except``````

Let me know if you might still need further assistance.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Resolver I

I've just tried this piece of code you provided, but i didn't manage to get the expected outcome.

: )

Super User

@Pedro503 , Based on what I got , try like

Churn =
VAR _NDaysCustomers =
CALCULATETABLE (
VALUES ( Fact[User] ),
DATEADD ( d_Calendar[Date], -30, DAY ),
USERELATIONSHIP ( d_Calendar[Date], Fact[End] )
)
VAR _IntervalCustomers =
CALCULATETABLE (
VALUES ( Fact[User] ),
DATESINPERIOD ( d_Calendar[Date], MIN ( d_Calendar[Date] ), -31, DAY )
)
VAR _Except =
COUNTROWS ( EXCEPT ( _NDaysCustomers, _intervalCustomers ) )
RETURN sumx(values(d_Calendar[Date]), calculate(_Except) )

Resolver I

Thanks for your response, @amitchandak .

I just tried what you suggested, but unfortunately I got the very same result as the example I provided.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.