Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Client | jan | feb | mar | apr | may |
10010 | 1 | ||||
10011 | 1 | 1 | |||
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 Code | jan/21 | feb/21 | mar/21 | apr/21 | may/21 |
10010 | 1 | 1 | 1 | 1 | |
10011 | 1 | 1 | 1 | 1 | |
10012 | 1 | 1 | 1 | ||
10013 | 1 | 1 | 1 | ||
10014 | 1 | 1 | 1 |
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 Code | jan/21 | feb/21 | mar/21 | apr/21 | may/21 |
10010 | 1 | 0 | 0 | 0 | |
10011 | 1 | 0 | 0 | 0 | |
10012 | 1 | 0 | 0 | ||
10013 | 1 | 0 | 0 | ||
10014 | 1 | 0 | 0 |
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,
@mbueno , Add +0 and Try
metric_final = [measure_Sale_12M]-[measure_Sale_12M-1]+0
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |