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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mbueno
Frequent Visitor

Count measure with date intelligence

Hello guys, I've a question with count in measures.

 

I've a initial matrix as distinct count of client that bought on these months.
Column: Date
Row: ID Client

 

ID Clientjanfebmaraprmay
10010 1   
10011 11  
10012  1  
10013  1  
10014  1  


In other words, the acumulated of sales in 12M is like: 

 

measure_Sale_12M = 
CALCULATE(DISTINCTCOUNT('General Key'[Client ID]),
DATESBETWEEN(Calendar[Date],
            DATEADD(
            LASTDATE(Calendar[Date]),-12,MONTH),
            LASTDATE(Calendar[Date]) 

))

 

Client Codejan/21feb/21mar/21apr/21may/21
10010 1111
10011 1111
10012  111
10013  111
10014  111

 

In this way, I've calculated the difference of month and month-1 to result like this: 

 

measure_Sale_12M-1 = 
CALCULATE([measure_Sale_12M],
 DATEADD(Calendario[Date], -1, MONTH)

)

 

and then

 

metric_final = [measure_Sale_12M]-[measure_Sale_12M-1]

 

resulting this:

Client Codejan/21feb/21mar/21apr/21may/21
10010 1000
10011 1000
10012  100
10013  100
10014  100

This way I got active client (result 0), new client (result 1) and lost client(-1) in table. 

 

But my problem is here. How can I count the 0? I'm just using matrix visual, I don't have table only for this operation, just a general database. How do I a measure to give count this?

In practical example, PBI show me this: 

mbueno_1-1663093071566.png


Thank you,


 

2 REPLIES 2
amitchandak
Super User
Super User

@mbueno , Add +0 and Try

metric_final = [measure_Sale_12M]-[measure_Sale_12M-1]+0

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hey amitchandak, thank you for the answer but it didn't work 😥

metric_final = [measure_Sale_12M]-[measure_Sale_12M-1]+0

Before: 

mbueno_1-1663159649390.png

After: 

mbueno_2-1663159706629.png

And then when I filter with "metric_final is 0" on september ... show me this, doesn't counting

 

mbueno_3-1663159828022.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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