Measure for new clients based on the year filter

[Anonymous]

Hello!

I have a table with clients and their respective contracts, each client can be a new client or a recurrent one. A new client is someone who had his/her contract for the first time - referent to the selected year in the year filter, for example: selecting the year "2022" on the filter, we have the contract(s) of the client X, who didn't have any other contracts prior to 2022, therefore he is a new client; however, if I select "2023" or higher, and he has another contract that year, he is not a new client anymore because he already had one in 2022, then he is a recurrent client. My question is: how can I make a measure to filter between new/recurrent clients? I need to distinguish how many clients and the total value of the contracts in each year, for both categories.

Thanks in advance!

[Anonymous]

Hi @Anonymous 

Maybe you can try this:

First of all, I create a set of sample:

Then I create a measure:

type = 
VAR _currentyear = YEAR(MAX('Table'[Date]))
VAR _currentname = SELECTEDVALUE('Table'[name])
VAR _previousname = CALCULATE(
                        MAX('Table'[Date]),
                        FILTER(
                            ALL('Table'),
                            YEAR('Table'[Date])<_currentyear
                            &&
                            'Table'[name]=_currentname
                            )
                        )
RETURN IF(
        ISBLANK(_previousname),
        "NEW",
        "RECURRENT"
        )

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ! Thanks for the help. The version that I'm using is older, so the SELECTEDVALUE function does not exist. Is it possible to make an alternative function? Additionally, the date and name fields are from separate tables, how can I change the FILTER function to that? 

[talespin]

hi @Anonymous ,

I am using adventureworks to simulate your scenario.

New or recurring customers

Measure

------------------

MNewOrRecurrent =

VAR _Year = SELECTEDVALUE(DimDate[CalendarYear])

VAR _ordersPriorYears = CALCULATE(COUNT(FactInternetSales[CustomerKey]), REMOVEFILTERS(DimDate[CalendarYear]), DimDate[CalendarYear] < _Year)

RETURN IF( ISBLANK([MSalesAmount]), BLANK(), IF( _ordersPriorYears > 0, "Recurring", "New") )

[Anonymous]

Hi @talespin, thanks for the help! However, I'm using an older version of Power BI, so the SELECTEDVALUE and REMOVEFILTERS fuctions doesn't exist. Having said that, is it possible to make an alternative DAX?

[talespin]

hi @Anonymous ,

Sorry not aware of alternative to SELECTEDVALUE to read slicers.

[Greg_Deckler]

@Anonymous See if this helps: Better Sales from New Customers - Microsoft Fabric Community