cancel
Showing results for
Did you mean:
Helper I

## Dynamic previous row value?

I have a few formulas that I am using to calculate customer count, churned customer count, and previous churned customer running total. What I am trying to do is figure out a way to allow for proper rolls ups for churn percentage calculations based on mont-year, quarter-year, and year. I have it correct for month-year; however, it does not pull the proper count of customers for the last quarter/year to calculate the percentage.

For example I want my results to look similar to the generic data below
On the Month Level
Month | Customer Count | Customer Count Previous
1          | 5                         |
2          | 6                         |  5
3          | 7                         | 6
4          | 8                         | 7
5          | 9                         | 8
6          | 10                       | 9

On the Quater Level
Q1          | 18                         |
Q2          | 27                         |  18

Formuals

``````Churned Count Helper =
COUNTROWS(
FILTER(
CALCULATETABLE(
VALUES(Customers[Tenant Id])),
CALCULATE([Total Billable Count],
DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-6),
MAX('Calendar'[Date])
)) = 0 &&
CALCULATE([Total Billable Count],
DATESBETWEEN('Calendar'[Date],
EDATE(MIN('Calendar'[Date]),-7),
EOMONTH(EDATE(MIN('Calendar'[Date]),-7),0)
)
)>0
)
)

Churned Count = SUMX(
VALUES('Calendar'[Month-Year]),
[Churned Count Helper]
)

Customer Count =
COUNTROWS (
FILTER (
SUMMARIZE ( XXXs, Customers[ID] ),
"minagStartdate", CALCULATE ( MIN ( 'XXX'[ExecutedDate] ) )
),
NOT(ISBLANK([minagStartdate]))
&& [minagStartdate] <= MAX ( 'Calendar'[Date] )
)
)

Customer Count Prev. Month = CALCULATE([Customer Count], DATEADD('Calendar'[Date], -1, MONTH))``````

1 ACCEPTED SOLUTION
Super User

Hi @DataStraine ,

You issue is that you are forcing the calculation to be based on the month since you are using the dateadd.

What can be done is to make use of a swith formula and then change the scope of your measure something similar to:

``````Customer Count Prev. Month =
SWITCH (
TRUE (),
ISINSCOPE ( 'Calendar'[Month] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, MONTH ) ),
ISINSCOPE ( 'Calendar'[Quarter] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, QUARTER ) ),
ISINSCOPE ( 'Calendar'[Year] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
)``````

also there is some blogs about custom periods and this type of calculations:

https://www.daxpatterns.com/month-related-calculations/

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

Hi @DataStraine ,

You issue is that you are forcing the calculation to be based on the month since you are using the dateadd.

What can be done is to make use of a swith formula and then change the scope of your measure something similar to:

``````Customer Count Prev. Month =
SWITCH (
TRUE (),
ISINSCOPE ( 'Calendar'[Month] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, MONTH ) ),
ISINSCOPE ( 'Calendar'[Quarter] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, QUARTER ) ),
ISINSCOPE ( 'Calendar'[Year] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
)``````

also there is some blogs about custom periods and this type of calculations:

https://www.daxpatterns.com/month-related-calculations/

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

Thank you for your response. This solved my issues. For people who may be looking for the answer in the future, if you are looking at a current year or quarter that has not yet ended, you'll need to add ENDOFMONTH and ENDOFYEAR to your calculations to get the right previous counts.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors