Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
karleek
Frequent Visitor

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

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. 

4 REPLIES 4
gmsamborn
Super User
Super User

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.

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! 

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

 

 

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?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors