cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ 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

LinkedIn Top Voice in Artificial Intelligece, 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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors