Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
Anonymous
Not applicable

Measures based on Current month and Previous Month

‎03-21-2023 11:11 PM

Hi,

 

Please find the below Table,

MonthUser ID
Dec1154
Dec3441
Dec3441
Jan1154
Jan1154
Jan3211
Jan3211
Jan5467
Feb1154
Feb3456
Feb3211
Feb3211

 

Need to calculate measure which is like, (Distinct count of user id where current month id = previous month id )/ Distinct count of Current Month id.

 

For the above, Jan month is 0.3 and feb month is 0.6.

 

MonthCalculation
Jan(1/3)=0.3
Feb(2/3)=0.6
Message 1 of 14
1,666 Views
0
13 REPLIES 13
Ashish_Mathur
Super User
Super User

‎03-23-2023 08:37 PM

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 14
1,577 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎03-24-2023 03:28 AM

Hi,

 

Applogise, written the wrong condition. Now Corrected it.Please see the requirement again.

Instead of Current month mentioned as previous month.

Condition is -->(Distinct count of user id where current month id = previous month id )/ Distinct count of Current Month id.

I tried in the attached file, but its not working. Can you create the measure in the file which i have attached here?

 

Please click below link to access file.

https://drive.google.com/drive/folders/1Y5Oe_tbpneLgdG3xSMChIEyjaqNIBS00?usp=sharing

 

Thank you,

Message 5 of 14
1,548 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎03-24-2023 07:37 PM

Hi,

Download the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 14
1,521 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎03-26-2023 10:23 PM

Hi,

 

No, this is not working. It will not show 100%, if all id matches then it will show 100%.

 

I need,D.Count of  (User id in current month matches with User id in Previous month month) which is divided by D.Count of User id in Current month.

 

@Ashish_Mathur @Anonymous 

 

Thanks,

Message 7 of 14
1,495 Views
0
Anonymous
Not applicable
In response to Anonymous

‎03-26-2023 11:23 PM

Hi,

Please use the below Table, and result needs to come as (Jan -0.3 and Feb 0.6)

 

DateMonth

 

User ID

27-12-2022Dec1154
27-12-2022Dec3441
07-12-2022Dec3441
27-01-2023Jan1154
02-01-2023Jan1154
02-01-2023Jan3211
16-01-2023Jan3211
22-01-2023Jan5467
12-02-2023Feb1154
21-02-2023Feb3456
21-02-2023Feb3211
16-02-2023Feb3211

 

Need to calculate measure which is like, (Distinct count of user id where current month id = previous month id )/ Distinct count of Current Month id.

 

For the above, Jan month is 0.3 and feb month is 0.6.

Jan month Id1154 matches with Dec and  Dist.ID count of Jan is 3. So for Jan (1/3=0.3)

Feb month id1154 and 3211 matches witn Jan and Dist ID count of feb is 3. So for Feb (2/3=0.6).

 

MonthCalculation
Jan(1/3)=0.3
Feb(2/3)=0.6

 

Thanks

Message 8 of 14
1,486 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎03-27-2023 03:00 AM

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 11 of 14
1,471 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎04-06-2023 05:46 AM

Hi,

 

I Have 2 Table,

Table-A
Date User ID Type
27-Dec-22 2211 Active
27-Dec-22 3441 Active
07-Dec-22 3441 Active
27-Jan-23 2211 Active
02-Jan-23 1154 Active
02-Jan-23 3211 Active
16-Jan-23 3211 Active
22-Jan-23 5467 Active
12-Feb-23 1233 Active
21-Feb-23 3456 Active
21-Feb-23 3211 Active
16-Feb-23 1154 Active

and

Table-B

Date User ID Type
27-Dec-22 1154 Passport
27-Dec-22 3441 Passport
07-Dec-22 3441 Passport
27-Jan-23 1154 Passport
02-Jan-23 1154 Passport
02-Jan-23 3211 Passport
16-Jan-23 3211 Passport
22-Jan-23 5467 Passport
12-Feb-23 1154 Passport
21-Feb-23 3456 Passport
21-Feb-23 3211 Passport
16-Feb-23 3211 Passport


Need to calculate ,

Numerator - Distinct count of User ID in Table A that matches with Prev month of User ID from Table B

Denominator - Prev month Distinct count of user ID from table B

Result need come like below,

Date Numerator Denominator Ret(Num/Den)
Dec
Jan 1 2 0.5
Feb 2 3 0.666666667

 

pls help here

@Ashish_Mathur 

Message 13 of 14
1,283 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎04-06-2023 04:14 PM

Hi,

I just cannot understand your requirement.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 14 of 14
1,262 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎03-26-2023 11:30 PM

Hi,

 

Please find the below Table. 

 

DateMonthUser ID
27-12-2022Dec1154
27-12-2022Dec3441
07-12-2022Dec3441
27-01-2023Jan1154
02-01-2023Jan1154
02-01-2023Jan3211
16-01-2023Jan3211
22-01-2023Jan5467
12-02-2023Feb1154
21-02-2023Feb3456
21-02-2023Feb3211
16-02-2023Feb3211

 

Need to calculate measure which is like, (Distinct count of user id where current month id = previous month id )/ Distinct count of Current Month id.

 

Example:

For the above,

Jan month, Id1154 matches with Dec and  Dist.ID count of Jan is 3. So for Jan (1/3=0.3)

Feb month, Id1154 and 3211 matches witn Jan and Dist ID count of feb is 3. So for Feb (2/3=0.6).

 

 results need to come like,

 

MonthCalculation
Jan(1/3)=0.3
Feb

(2/3)=0.6

@amitchandak  @Greg_Deckler  

 

Thanks,

Message 12 of 14
1,489 Views
0
Anonymous
Not applicable
In response to Anonymous

‎03-27-2023 02:05 AM

Hi @Anonymous ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create three measures. 

Measure 2 = var a=SUMMARIZE(FILTER(ALLSELECTED('Table'),MONTH('Table'[date])=MONTH(EOMONTH(SELECTEDVALUE('Table'[date]),-1))),[User ID])
var b=CALCULATE(DISTINCTCOUNT('Table'[User ID]),MONTH('Table'[date])=MONTH(SELECTEDVALUE('Table'[date])),'Table'[User ID] in a)
return b
Measure 3 = DISTINCTCOUNT('Table'[User ID])
Measure 4 = DIVIDE('Table'[Measure 2],'Table'[Measure 3],0)

(3) Then the result is as follows.

vtangjiemsft_0-1679907859820.png

 

Best Regards,

Neeko Tang

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

Measures based on Current month and Previous Month.pbix
Message 9 of 14
1,475 Views
0
Anonymous
Not applicable
In response to Anonymous

‎04-06-2023 05:48 AM

Hi,

 

I Have 2 Table,

Table-A
Date User ID Type
27-Dec-22 2211 Active
27-Dec-22 3441 Active
07-Dec-22 3441 Active
27-Jan-23 2211 Active
02-Jan-23 1154 Active
02-Jan-23 3211 Active
16-Jan-23 3211 Active
22-Jan-23 5467 Active
12-Feb-23 1233 Active
21-Feb-23 3456 Active
21-Feb-23 3211 Active
16-Feb-23 1154 Active

and

Table-B

Date User ID Type
27-Dec-22 1154 Passport
27-Dec-22 3441 Passport
07-Dec-22 3441 Passport
27-Jan-23 1154 Passport
02-Jan-23 1154 Passport
02-Jan-23 3211 Passport
16-Jan-23 3211 Passport
22-Jan-23 5467 Passport
12-Feb-23 1154 Passport
21-Feb-23 3456 Passport
21-Feb-23 3211 Passport
16-Feb-23 3211 Passport


Need to calculate ,

Numerator - Distinct count of User ID in Table A that matches with Prev month of User ID from Table B

Denominator - Prev month Distinct count of user ID from table B

Result need come like below,

Date Numerator Denominator Ret(Num/Den)
Dec
Jan 1 2 0.5
Feb 2 3 0.666666667

 

Pls help here

 

@Anonymous 

Message 10 of 14
1,282 Views
0
Anonymous
Not applicable

‎03-23-2023 08:19 PM

Hi @Anonymous ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Measure = var a=SUMMARIZE(FILTER(ALLSELECTED('Table'),MONTH('Table'[date])=MONTH(EOMONTH(SELECTEDVALUE('Table'[date]),-1))),[User ID])
var b=CALCULATE(DISTINCTCOUNT('Table'[User ID]),MONTH('Table'[date])=MONTH(SELECTEDVALUE('Table'[date])),'Table'[User ID] in a)
return IF(b=0,0,DIVIDE(b,COUNTROWS(a)))

(3)We can create a calculated column.

Month name = FORMAT('Table'[date],"mmm")

(4) Then the result is as follows.

vtangjiemsft_0-1679642015878.png

 

 

Best Regards,

Neeko Tang

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

Measures based on Current month and Previous Month.pbix
Message 2 of 14
1,583 Views
0
Anonymous
Not applicable
In response to Anonymous

‎03-24-2023 03:27 AM

Hi,

 

Applogise, written the wrong condition. Now Corrected it.Please see the requirement again.

Instead of Current month mentioned as previous month.

Condition is -->(Distinct count of user id where current month id = previous month id )/ Distinct count of Current Month id.

I tried in the attached file, but its not working. Can you create the measure in the file which i have attached here?

 

Please click below link to access file.

https://drive.google.com/drive/folders/1Y5Oe_tbpneLgdG3xSMChIEyjaqNIBS00?usp=sharing

 

 

Message 3 of 14
1,548 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All