How to create new and repeated users

[ahpatil11]

Hi All,

This is my first Question. Please help me

I wanted to have New and Repeated User for my data and also total transaction done for new and repeated user. The thing is that I had joined 2 tables (on USER_REFERENCE_NUMBER) to get below table

USER_CREATION_DATE and USER_REFERENCE_NUMBER are from Customer table and TRANSACTION_CREATED_AT and TXN_AMOUNT are from transaction table

Please provide me the solution. 

[ahpatil11]

Hi @FreemanZ 

Thank you for the solution. It Works

[FreemanZ]

hi @ahpatil11 

depends on how do you define new and repeat. typically it is in year, if so, you may try to write two measures like:

TransactionNew =

VAR _listthisyear=

CALCULATETABLE(

     VALUES(TableName[UserNumber]),      YEAR(TableName[UserCreationDate])=YEAR(TODAY())

)

VAR _listlastyear = 

CALCULATETABLE(

     VALUES(TableName[UserNumber]), 

     YEAR(TableName[UserCreationDate])=YEAR(TODAY())-1

)

VAR _new = 

EXCEPT(_listthisyear, _listlastyear)

RETURN

CALCULATE(

     SUM(TableName[Amount]), 

     _new

)

TransactionRepeat =

VAR _listthisyear=

CALCULATETABLE(

VALUES(TableName[UserNumber]), YEAR(TableName[UserCreationDate])=YEAR(TODAY())

)

VAR _listlastyear =

CALCULATETABLE(

     VALUES(TableName[UserNumber]),

YEAR(TableName[UserCreationDate])=YEAR(TODAY())-1

)

VAR _repeat =

INTERSECT(_listthisyear, _listlastyear)

RETURN

CALCULATE(

     SUM(TableName[Amount]),

    _repeat

)

[ahpatil11]

Hi @FreemanZ 

Thank you for your reply 

I wanted to show total transactions done in months by new and repeated users. 

I tried your solution but it is coming has blank

can you help me with this

[FreemanZ · Accepted solution]

hi @ahpatil11 

then try:

TransactionNew =
VAR _list1=
CALCULATETABLE(
     VALUES(TableName[UserNumber]),      
     YEAR(TableName[UserCreationDate])=YEAR(TODAY()),
     MONTH(TableName[UserCreationDate])=MONTH(TODAY())
)
VAR _list2=
CALCULATETABLE(
     VALUES(TableName[UserNumber]),
     YEAR(TableName[UserCreationDate])=YEAR(TODAY()),
    MONTH(TableName[UserCreationDate])=MONTH(TODAY())-1
)
VAR _new =
EXCEPT(_list1,_list2)
RETURN
CALCULATE(
     SUM(TableName[Amount]),
     _new
)

TransactionRepeat =
CALCULATETABLE(
     VALUES(TableName[UserNumber]),      
     YEAR(TableName[UserCreationDate])=YEAR(TODAY()),
     MONTH(TableName[UserCreationDate])=MONTH(TODAY())
)
VAR _list2=
CALCULATETABLE(
     VALUES(TableName[UserNumber]),
     YEAR(TableName[UserCreationDate])=YEAR(TODAY()),
    MONTH(TableName[UserCreationDate])=MONTH(TODAY())-1
)
VAR _repeat =
INTERSECT(_listthisyear, _listlastyear)
RETURN
CALCULATE(
     SUM(TableName[Amount]),
    _repeat
)

in case of issue, please conside post the sample data as text in the reply.