Power bi dax - TABLE

[carolinesb]

Hello, can anyone help me with the dax formula?

I need it to be classified this way: not_buy = If you have SALE VALUE filled in in the year 2023 return "prospect"

The name of the table is Sales and the name of the column with sales value is sales value and the year is year, i have the column with the code of the customer.

I also need help with the formula, every customer who purchased in 2023 and already purchased in 2024 returns “repurchased”

all customers who purchased in 2024, but did not purchase in 2023, but purchased in other years, classify as “new”

the customer purchased more than once, but does not take into account the year, classify it as “recurrence”

column of SALES VALUES

  columb of year

[Anonymous · Accepted solution]

Hi  @carolinesb ,

Here are the steps you can follow：

1. Create calculated column.

Test1 =
var _column=
SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])&&[sales value]<>BLANK()),"Year",[Year])
var _discount=
CALCULATE(
    DISTINCTCOUNT('Table'[Year]),
    FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])&&[sales value]<>BLANK()))
return
SWITCH(
    TRUE(),
    2023 in _column &&  2024 in _column,"repurchased",
    NOT(2023) in _column &&  2024 in _column && _discount>1,"new",BLANK())

Test2 =
var _column=
SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])&&[sales value]<>BLANK()),"Year",[Year])
return
IF(
    2023 in _column,"prospect")

2. Result:

Best Regards,

Liu Yang

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

[Daniel29195]

Hello @carolinesb ,

given that you have a table called sales , with the following stucture

i would also suggest ( if they are not yet created ) to create dimcustomers and dimyear  and link these 2 tables to sales via, customer and year columns so that you create w star schema which is not recommended to work with flattened tables.

year , sales value , customer

i assume you need to create a visual, with the following columns : 
year , customer, sum(sales value ) ,  customer type 

you can create the following measure  : 

customer type = 

var currentyear =  current(today()) -- 2024

var prev_year = currentyear - 1      -- 2023

var datasource = 

addcolumns(

          calculatetable(
                   summarize(

                              sales, 

                               dimcustomer[cust_name],

                               dimyear[col_name]

                   ),

            allselected(dimyear[year])
          ),

"count" , calculate(countrows(sales))
)

RETURN

switch(

true(),

{currentyear, prev_year} in datasource   , "repurchased",

{current_year} in datasource && NOT {prev_year} in datasource  , "new",

{prev_year} in datasource ,"prospect"
)


the last condition im not sure what you mean by it.

NB : please map the table names and columns to your model tables names and column names. 

let me know if it works for you.

if you need any help , i will be more than happy to assist you. 

best regards.

[Ritaf1983]

Hi @carolinesb 
Take a look at new and repeat customer analysis patterns at the linked blog posts:

https://blog.finance-bi.com/power-bi-new-and-repeat-customers/

https://www.daxpatterns.com/new-and-returning-customers/

and video tutorial:

https://www.youtube.com/watch?v=WCt5IFsPUCA

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

[carolinesb]

The dax IF('Vendas', Ano[Ano], "prospectar",0) are correct?