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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
giuliapiazza94
Helper IV
Helper IV

Customers found / lost measure

Hi guys,

i need to create a measure or field to identify which customers have bought this year but not last year (found customer) and which customers bought last year and not this year (lost customer).

This measure/field has to be a dynamic measure/field, so if I choose "2021" in filter object I'd like to see found customers and lost customers in 2021. The same if i choose 2022, etc.

 

I've tried to create a measure but if I do a drill down it doesn't work (page 2), so i tried to create a field and it works but i had to create a field for each time coparison (page 1) and I don't like it.

 

Can you help me?

Thank you all 😁

 

file pbix

https://drive.google.com/file/d/1_Uoaz_WTofGT7YUBdMnxbpupp-odWO3R/view?usp=sharing

3 REPLIES 3
Greg_Deckler
Super User
Super User

@giuliapiazza94 Try this:

 

Measure = 
    VAR __Customer = MAX('Customer'[K_CodCli])
    VAR __Year = MAX('Calendar'[Year])
    VAR __Table = SUMMARIZE(FILTER(ALL('Sales'),YEAR('Sales'[K_DataRegistraz]) = __Year - 1),[K_CodCli],"Sales",SUM('Sales'[Importo totale]))
    VAR __Table1 = SUMMARIZE(FILTER(ALL('Sales'),YEAR([K_DataRegistraz]) = __Year),[K_CodCli],"Sales",SUM('Sales'[Importo totale]))
    VAR __LastYearCust = 
        SELECTCOLUMNS(
            FILTER(__Table,[Sales] > 0 && [Sales] <> BLANK()),
            "ID",[K_CodCli]
        )
    VAR __ThisYearCust = 
        SELECTCOLUMNS(
            FILTER(__Table1,[Sales] > 0 && [Sales] <> BLANK()),
            "ID",[K_CodCli]
        )
RETURN
    SWITCH(TRUE(),
        __Customer IN __ThisYearCust && NOT(__Customer IN __LastYearCust),"Found",
        __Customer IN __LastYearCust && NOT(__Customer IN __ThisYearCust),"Lost",
        BLANK()
    )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

If I use drill down It doesn't work, like my measure.

A matrix, in the rows there is customer and item

Customer 10007 - CONDOMINIO PITAGORA has bought 100.000€.

If i use drill down it shows all item, while i'd like to see only items that he has bought

 

giuliapiazza94_0-1663666471808.png

 

giuliapiazza94_1-1663666491932.png

 

 

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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