Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have the following dataset in Power BI and I'm interested in adding a column to see how many times a client visited in a calendar month.
Data:
Client_ID | Date | Month |
001 | 1/5/2014 | 2014/1 |
002 | 1/6/2014 | 2014/1 |
003 | 1/7/2014 | 2014/1 |
004 | 1/8/2014 | 2014/1 |
005 | 1/10/2014 | 2014/1 |
006 | 1/11/2014 | 2014/1 |
007 | 1/12/2014 | 2014/1 |
008 | 1/13/2014 | 2014/1 |
001 | 1/15/2014 | 2014/1 |
002 | 1/17/2014 | 2014/1 |
004 | 1/20/2014 | 2014/1 |
006 | 1/21/2014 | 2014/1 |
001 | 1/21/2014 | 2014/1 |
002 | 1/22/2014 | 2014/1 |
006 | 1/23/2014 | 2014/1 |
002 | 2/1/2014 | 2014/2 |
001 | 2/3/2014 | 2014/2 |
003 | 2/4/2014 | 2014/2 |
Wanted Result:
Client_ID | Date | Month | N Visits that month |
001 | 1/5/2014 | 2014/1 | 3 |
002 | 1/6/2014 | 2014/1 | 3 |
003 | 1/7/2014 | 2014/1 | 1 |
004 | 1/8/2014 | 2014/1 | 2 |
005 | 1/10/2014 | 2014/1 | 1 |
006 | 1/11/2014 | 2014/1 | 3 |
007 | 1/12/2014 | 2014/1 | 1 |
008 | 1/13/2014 | 2014/1 | 1 |
001 | 1/15/2014 | 2014/1 | 3 |
002 | 1/17/2014 | 2014/1 | 3 |
004 | 1/20/2014 | 2014/1 | 2 |
006 | 1/21/2014 | 2014/1 | 3 |
001 | 1/21/2014 | 2014/1 | 3 |
002 | 1/22/2014 | 2014/1 | 3 |
006 | 1/23/2014 | 2014/1 | 3 |
002 | 2/1/2014 | 2014/2 | 1 |
001 | 2/3/2014 | 2014/2 | 1 |
003 | 2/4/2014 | 2014/2 | 1 |
Thanks in advance!
Solved! Go to Solution.
I got an error on your column, but I figured it out. Here is the column I created:
Monthly Visits =
CALCULATE(
COUNT(Congregate_Only_Logs[Client_ID]),
FILTER(Congregate_Only_Logs,
Congregate_Only_Logs[Month]=EARLIER(Congregate_Only_Logs[Month])),
FILTER(Congregate_Only_Logs,
Congregate_Only_Logs[Client_ID]=EARLIER(Congregate_Only_Logs[Client_ID])))
Thanks!!!
HI @Anonymous
Try this column
N Visits = CALCULATE ( COUNT ( TableName[Date] ), FILTER ( ALLEXCEPT ( TableName, TableName[Client_ID] ), MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) ) ) )
In the same example, how can we calculate the visit in the last X days?
Could you help with that please?
Thank you
Ilky
I got an error on your column, but I figured it out. Here is the column I created:
Monthly Visits =
CALCULATE(
COUNT(Congregate_Only_Logs[Client_ID]),
FILTER(Congregate_Only_Logs,
Congregate_Only_Logs[Month]=EARLIER(Congregate_Only_Logs[Month])),
FILTER(Congregate_Only_Logs,
Congregate_Only_Logs[Client_ID]=EARLIER(Congregate_Only_Logs[Client_ID])))
Thanks!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |