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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
capibaraya
Regular Visitor

month over base change

Hi! I'm looking for the solution of the following task. 

 

I have table with columns: period, client id (those who have used service this month), month of first visit. I have a histogram for visits with period as X and client id count as Y. Also I have a slicer with the month of first visit. So I can choose those clients, who had used service for the first time in May, and see how many of them used it again in July, August, October etc. Now I need to add a line (to calculate a measure) to show, which percentage of May-clients returned back in  July, August, October etc. So I have to choose the base month for calculation depending on the value of 'month of first visit' choosen on slicer. I'll be thankfull if someone can help me. 

 

mon  count desired column

jan    0      0%

feb    0      0%

mar   0      0%

apr   100   100%

may  80     80%

jun    71     71%

july   10     10%

aug   2       2%

sep   0        0%

oct    0        0%

nov   0        0%

dec   0        0%

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @capibaraya,

 

Please refer to below measures:

count client per month = DISTINCTCOUNT('Test Tb1'[Client id])
Count all in selcted month = CALCULATE(DISTINCTCOUNT('Test Tb1'[Client id]),ALL('Test Tb1'[Month]))

percentage = [count client per month]/[Count all in selcted month]

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @capibaraya,

 

Please refer to below measures:

count client per month = DISTINCTCOUNT('Test Tb1'[Client id])
Count all in selcted month = CALCULATE(DISTINCTCOUNT('Test Tb1'[Client id]),ALL('Test Tb1'[Month]))

percentage = [count client per month]/[Count all in selcted month]

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.