topic Re: How to create new and repeated users in DAX Commands and Tips

How to create new and repeated users

ahpatil11 — Thu, 12 Jan 2023 17:24:33 GMT

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.

Re: How to create new and repeated users

FreemanZ — Thu, 12 Jan 2023 21:47:28 GMT

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

)

Re: How to create new and repeated users

ahpatil11 — Fri, 13 Jan 2023 06:01:14 GMT

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

Re: How to create new and repeated users

FreemanZ — Fri, 13 Jan 2023 05:54:50 GMT

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.

Re: How to create new and repeated users

ahpatil11 — Wed, 18 Jan 2023 09:19:34 GMT

Hi FreemanZ

Thank you for the solution. It Works