Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to create new and repeated users

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 tabl...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    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.

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Anonymous 

 
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
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    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.