Forum Discussion

mbueno's avatar
mbueno
Frequent Visitor
4 years ago

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: 


Thank you,


 

2 Replies

    • mbueno's avatar
      mbueno
      Frequent Visitor

      Hey amitchandak, thank you for the answer but it didn't work ðŸ˜¥

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

      Before: 

      After: 

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