cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataStraine
Helper I
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 (
        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))

 



1 ACCEPTED SOLUTION
MFelix
Super User
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://radacad.com/previous-dynamic-period-dax-calculation

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
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://radacad.com/previous-dynamic-period-dax-calculation

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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