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.
My formula below works properly for customers that have Revenue > 0 in all 11 months between Jan 1 2018 and Nov 30 2018. I am dividing by 11 (highlighted in red below).
Solved! Go to Solution.
Hello @Anonymous
You can use some variables and filters in your measure to get the count of the number of months where the revenue <> 0 for each customer.
AvgRevenueJanNov = VAR Jan = DATE ( 2018, 1, 1 ) VAR Nov = DATE ( 2018, 11, 30 ) VAR RowCount = CALCULATE ( COUNTROWS ( VALUES ( CustomerMargin[Date] ) ), DATESBETWEEN ( CustomerMargin[Date], Jan, Nov ), CustomerMargin[Revenue] <> 0 ) RETURN DIVIDE ( CALCULATE ( SUM ( CustomerMargin[Revenue] ), DATESBETWEEN ( CustomerMargin[Date], Jan, Nov ) ), RowCount, 0 )
Hello @Anonymous
You can use some variables and filters in your measure to get the count of the number of months where the revenue <> 0 for each customer.
AvgRevenueJanNov = VAR Jan = DATE ( 2018, 1, 1 ) VAR Nov = DATE ( 2018, 11, 30 ) VAR RowCount = CALCULATE ( COUNTROWS ( VALUES ( CustomerMargin[Date] ) ), DATESBETWEEN ( CustomerMargin[Date], Jan, Nov ), CustomerMargin[Revenue] <> 0 ) RETURN DIVIDE ( CALCULATE ( SUM ( CustomerMargin[Revenue] ), DATESBETWEEN ( CustomerMargin[Date], Jan, Nov ) ), RowCount, 0 )
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |