Dynamic previous row value?

[DataStraine]

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))

[MFelix · Accepted solution]

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/

[DataStraine]

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.