Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
hi @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
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
38 | |
22 | |
18 | |
15 | |
11 |