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.
I've been stuck on a problem for some time and can't figure out if it's not possible with Power BI given my current data set or if my knowledge is lacking. I'm trying to create a report for Dynamics CRM that displays opportunity data by month. Where I'm having trouble is summarizing calculated metrics by month and then running a moving average calculation over those summarized metrics.
The input data looks something like this:
I know the date the record was created, when it was closed, and its estimated value.
What I'm trying to create is something like this using the data above:
CALCULATE( COUNTROWS(Opportunities), FILTER( Opportunities, Opportunities[Created Date] <= MAX('Date'[Date]) ) )
CALCULATE( COUNTROWS(Opportunities), FILTER( Opportunities, Opportunities[Is Closed] ), FILTER( Opportunities, Opportunities[Close Date] <= MAX('Date'[Date]) ) )
[Total Opportunities] - [Total Closed Opportunities]
My question is, is it possible to achieve the last three bullets given the input? Will I need to create a snapshot table of some sort to do this because of the metrics involved? If this is possible, how can I do it? Nothing I've tried thus far has worked!
Thank you for your help!
Solved! Go to Solution.
Hi @steveplatz,
You can refer to below formulas:
Measures: Total open estimated value = CALCULATE( SUM(Opportunities[Estimated Value]), FILTER( Opportunities, Opportunities[Create Date] <= MAX('Date'[Date])&&OR([Is Closed]<>"Yes",Opportunities[Close Date] > MAX('Date'[Date])&& [Is Closed]="Yes") ) ) Average open estimated value = [Total open estimated value] / [Total open opportunities] 3 Month average open estimated value = var curret=MAX('Date'[Date]) return AVERAGEX(FILTER(ALL('Date'),[Date]>=DATE(YEAR(curret),MONTH(curret)-3,DAY(curret))&&[Date]<=DATE(YEAR(curret),MONTH(curret),DAY(curret))),[Average open estimated value])
BTW, your formula "Total closed opportunities" seems not works on my side, I modified to below formula:
Total closed opportunities = CALCULATE( COUNTROWS(Opportunities), FILTER( Opportunities, Opportunities[Is Closed]="Yes" ), FILTER( Opportunities, Opportunities[Close Date] <= MAX('Date'[Date]) ) )
Regards,
Xiaoxin Sheng
Hi @steveplatz,
You can refer to below formulas:
Measures: Total open estimated value = CALCULATE( SUM(Opportunities[Estimated Value]), FILTER( Opportunities, Opportunities[Create Date] <= MAX('Date'[Date])&&OR([Is Closed]<>"Yes",Opportunities[Close Date] > MAX('Date'[Date])&& [Is Closed]="Yes") ) ) Average open estimated value = [Total open estimated value] / [Total open opportunities] 3 Month average open estimated value = var curret=MAX('Date'[Date]) return AVERAGEX(FILTER(ALL('Date'),[Date]>=DATE(YEAR(curret),MONTH(curret)-3,DAY(curret))&&[Date]<=DATE(YEAR(curret),MONTH(curret),DAY(curret))),[Average open estimated value])
BTW, your formula "Total closed opportunities" seems not works on my side, I modified to below formula:
Total closed opportunities = CALCULATE( COUNTROWS(Opportunities), FILTER( Opportunities, Opportunities[Is Closed]="Yes" ), FILTER( Opportunities, Opportunities[Close Date] <= MAX('Date'[Date]) ) )
Regards,
Xiaoxin Sheng
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |