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 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. 

 

  • 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.

4 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi FreemanZ 

    Thank you for the solution. It Works