Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mregnide
Regular Visitor

Number of Clients who had at least one visit each month

This seems like a tricky measure to create and I have no idea where to start.  I have a table with Client and Visit Date.  Clients can come in multiple times per month. I'm trying to figure out

 

1) Which clients had at least one visit per month.

2) Count of those clients

3) Total number of visits by those clients.

 

Attached is a small example of what I'm trying to accomplish.EachMonth.JPG

 

Thanks in advance for the help.

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @mregnide ,

 

1.Create a calculated column to display the Month. And create another calculated column to display clients who had at least one visit per month.

 

Month = MONTH([Visit Date])
Clients who had at least one visit per month =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        ALLEXCEPT ( 'Table', 'Table'[Client] )
    )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Month] ), ALL ( 'Table' ) ),
    1
)

 

4.png

 

2.Put the Clients who had at least one visit per month column into the Filters. Set show items when the value is 1.

5.png

 

3.Create a measure to count the number of cilents who had at least one visit per month.

 

Count of clients who had at least one visit per month =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Client] ),
    FILTER ( 'Table', [Clients who had at least one visit per month] = 1 )
)

 

6.png

 

4.Create a measure to count the total number of visits by those clients.

 

Total number of visits by those clients = COUNT('Table'[Clients who had at least one visit per month])

 

7.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @mregnide ,

 

1.Create a calculated column to display the Month. And create another calculated column to display clients who had at least one visit per month.

 

Month = MONTH([Visit Date])
Clients who had at least one visit per month =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        ALLEXCEPT ( 'Table', 'Table'[Client] )
    )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Month] ), ALL ( 'Table' ) ),
    1
)

 

4.png

 

2.Put the Clients who had at least one visit per month column into the Filters. Set show items when the value is 1.

5.png

 

3.Create a measure to count the number of cilents who had at least one visit per month.

 

Count of clients who had at least one visit per month =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Client] ),
    FILTER ( 'Table', [Clients who had at least one visit per month] = 1 )
)

 

6.png

 

4.Create a measure to count the total number of visits by those clients.

 

Total number of visits by those clients = COUNT('Table'[Clients who had at least one visit per month])

 

7.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-stephen-msft , that was very helpful!

 

One quick question: is it possible by changing the calculated column or writing a measure so that if I change the date range via slicer that the count and which clients meet the critera changes based on that date range? 

Hi @mregnide ,

 

It is achievable, as long as you use the visit date as a slicer, there is no need to change the calculated column or the measure.

 

result930116.gif

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vanessafvg
Super User
Super User

can you provide the data in text format?




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I could not see an option to upload a csv, but here is the table.

 

ClientVisit Date
A1/1/2020
B1/5/2020
C1/15/2020
D1/20/2020
A2/1/2020
B2/15/2020
A3/1/2020
B3/5/2020
C3/15/2020
A4/1/2020
B4/5/2020
D4/20/2020

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.