YOY % Growth by Distinct Customer ID

[DataScope06]

I have a table with customer IDs by date. Each customer falls in a different category. The data looks like this:

customerID	category	created_at
1	Red	1/1/2021
2	Red	5/1/2021
3	Blue	6/1/2021
4	Red	1/1/2022
5	Red	4/1/2022
6	Red	5/1/2022
7	Red	6/1/2022
8	Blue	7/1/2022
9	Blue	8/1/2022
10	Blue	8/1/2022

Using the customerID to count customers, I need to show the YOY growth in the Red and Blue categories.

This is a classic problem for this calculation:

Year-over-Year Growth = (Current count – Previous year count) X 100 / Previous year count).

Using the above example data, from 2021 to 2022, customer growth increased 100% in the Red category and 200% in the Blue category. 

When trying to use Dax functions I get a duplicate date error. Or when I try to incorporate distinct count into the sum, the previous year shows a value of one, which I assume means the formula is just counting the previous year's value as one distinct number.

[DataScope06]

Thank you, @Anonymous

Your solution works to return the percent difference between current and previous year. How would this calculation be adjusted to show the percent difference in running totals?

For example, in 2020 there are 10 new customers, then in 2021, 15 new customers and in 2022 10 new customers.

So the running sum of all customers are 10 in 2020, 25 customers in 2021, and 45 customers in 2022.

In that situation the growth rates would be:

2021: (25-10)/10 = 150%
2020: (45-25)/25 = 80%

[Anonymous]

Hi @DataScope06 ,

Please try:

Year-over-Year Growth =

VAR _year =

    MAX ( 'Calendar'[Year] )

VAR _category =

    MAX ( 'Table'[category] )

VAR _current_count =

    CALCULATE (

        COUNT ( 'Table'[customerID] ),

        'Calendar'[Year] <= _year,

        'Table'[category] = _category

    )

VAR _previous_year_count =

    CALCULATE (

        COUNT ( 'Table'[customerID] ),

        'Calendar'[Year] = _year - 1,

        'Table'[category] = _category

    ) + 0

VAR _growth =

    DIVIDE ( _current_count - _previous_year_count, _previous_year_count )

RETURN

    _growth

Best Regards,
Gao
Community Support Team

[DataScope06 · Accepted solution]

@Anonymous, Your last solution is close, but something is wrong with the calculation. See the screenshot below with numbers from the last formula you submitted. 

For example, the 2021 value for YOY % Growth of Running Total should be 28.8% by this formula:

((Current Year # - Previous Year #)/(Previous Year #))*100:

((746-579)/(579))*100 = 28.8

***Edit, the correct % increase is returned if you make the change to VAR _previous_year_count in bold below:

Year-over-Year Growth =

VAR _year =

    MAX ( 'Calendar'[Year] )

VAR _category =

    MAX ( 'Table'[category] )

VAR _current_count =

    CALCULATE (

        COUNT ( 'Table'[customerID] ),

        'Calendar'[Year] <= _year,

        'Table'[category] = _category

    )

VAR _previous_year_count =

    CALCULATE (

        COUNT ( 'Table'[customerID] ),

        'Calendar'[Year] <= _year - 1,

        'Table'[category] = _category

    ) + 0

VAR _growth =

    DIVIDE ( _current_count - _previous_year_count, _previous_year_count )

RETURN

    _growth

[Anonymous]

Hi @DataScope06 

Great!😎
Please consider accepting your reply to close this thread. thanks.

Best Regards,
Gao
Community Support Team

[Anonymous]

Hi @DataScope06 ,

Please try this measure:

Year-over-Year Growth = 
VAR _year =
    MAX ( 'Calendar'[Year] )
VAR _category =
    MAX ( 'Table'[category] )
VAR _current_count =
    CALCULATE (
        COUNT ( 'Table'[customerID] ),
        'Calendar'[Year] = _year,
        'Table'[category] = _category
    )
VAR _previous_year_count =
    CALCULATE (
        COUNT ( 'Table'[customerID] ),
        'Calendar'[Year] = _year - 1,
        'Table'[category] = _category
    ) + 0
VAR _growth =
    DIVIDE ( _current_count - _previous_year_count, _previous_year_count )
RETURN
    _growth

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[DataScope06]

@amitchandak When I use the calculation you provided, the count for customers is 1. But the value should be in the hundreds of thousands.

Also, the created_at field does not appear to join the date table properly. In my dataset, created_at is a datetime field that goes back to 2017. But only dates in 2022 appear to be joining together.

Any ideas?

[amitchandak]

@DataScope06 , Use a separate date table. Prefer to use a separate category table too Both should be joined with your table

YTD Sales = CALCULATE(count(Table[CustomerID]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(count(Table[CustomerID]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

or

This year Sales = CALCULATE(count(Table[CustomerID]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(count(Table[CustomerID]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

then

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.