Forum Discussion
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 (
ADDCOLUMNS (
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))
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://radacad.com/previous-dynamic-period-dax-calculation
https://www.daxpatterns.com/month-related-calculations/
2 Replies
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://radacad.com/previous-dynamic-period-dax-calculation
https://www.daxpatterns.com/month-related-calculations/
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.
