YoY Measurements - Measure to Exclude Clients that don't have data for same period previous year

[karleek]

I have 38 clients, with unique identifier, and as I work to get accurate YoY reporting, I need to exclude clients that came on mid year. For example, say in 2022 I had 5 clients and $1M in sales, and then in 2023 I had 8 clients with $2M in sales, I am not comparing apples to apples. I need to be comparing the same 5 clients from 2022 to the same 5 clients in 2023 (exlcuding the 3 new clients in 2023) in order to get accurate YoY variances for our industry. 

For the client I do have their start date so could use that as part of the filter or exlcusion data, but from there I am at a loss. Any guidance would be much appreciated. 

[gmsamborn]

Hi @karleek 

You might find this interesting.

https://www.daxpatterns.com/like-for-like-comparison/#:~:text=The%20like%2Dfor%2Dlike%20comparison,c....

Let me know if you have any questions.

[karleek]

Thank you for this link this is exactly what I needed. I do have a question.

The tutorial in the link you provided goes over how to compare Year to Year, which is great and gets me party of the way to my solution, but I also need to be able to compare specific month to month in the same year, either look at January 2022 compared to January 2023 for only customers Active in both of those month. 

Do you how I can add Month into the existing table from the tutorial, whereas the example only has Year. 

Everything I have tried thus far hasn't work, so hoping you might have some idea. Thanks! 

[gmsamborn]

Hi @karleek 

After comparing the pattern with your updated requirements, I realized the pattern wasn’t for your purpose.

Using the same dataset as the tutorial, I came up with the following.

(I’m not sure how to rename columns but I was able to give you a custom title for the visual.  )

Monthly.pbix

[karleek]

I was able to get my month added into my table that follows the initialy article on like for like you provided me, but now the issue I am facing is I cannot look at 2024 thus far since I don't have a complete year of data. This is my measure that works beautifully up until January 1, 2024.

Same Customer Orders =

var LiveCustomer =

CALCULATETABLE(

    FILTER(

        ALLSELECTED('Customer Table'[Final Business ID]),

        CALCULATE(

            SELECTEDVALUE('All Customer'[Status])

    ) = "Live"),

            ALLSELECTED('Date'))

            var FilterCustomer =

            TREATAS(LiveCustomer, 'CustomerTable'[Final Business ID])

            var Result =

            CALCULATE(

                [Total Exchanges],

                KEEPFILTERS(filterCustomer))

                Return

                Result

Any insight into what I need to change within this measure so that I can look at 2024 data YTD compared to previous years same period?