Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 )
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |