Forum Discussion

carolinesb's avatar
carolinesb
Regular Visitor
2 years ago
Solved

Power bi dax - TABLE

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

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

    • carolinesb's avatar
      carolinesb
      Regular Visitor
      2 years ago

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

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

     

     

           

             

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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