Hey,
I have a formula which calculates, at a customer by customer level, the value of their business if new. I need to use a sumx for this to iterate down the customer table in order to check if it is a new customer within the date period selected.
Wins (Current Year) =
var Comp1Sel = SELECTEDVALUE('Measure Select'[Measure Select])
var Comp2Sel = SELECTEDVALUE('Measure Select (Second Table)'[Measure Select])
var mindate = STARTOFYEAR('Date Mapping'[Month])
var maxdate = ENDOFYEAR('Date Mapping'[Month])
return
if(Comp1Sel="Prior Year",
sumx('BU Master Data',CALCULATE([Comparator 2],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate) -
CALCULATE([Comparator 1],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate))
,
sumx('BU Master Data',if(CALCULATE([Comparator 1 Exists?],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate)=0,
CALCULATE([Comparator 2],'BU Master Data'[Open Month (No Blanks)]>=mindate,'BU Master Data'[Open Month (No Blanks)]<=maxdate),0))
)
-CALCULATE([Comparator 1],'BU Master Data'[BU Parent Group]="Unknown Wins")
I also have multiple other efficiency formulas. These cannot be a SUMX as the total by each customer will not tie to the total across the business due to blended rates.
F&B Efficiency = 'Non Core Measures: Walk & Efficiencies'[Comparator 2 Revenue (Efficiency)]*(([Comparator GP % (Efficiency)]-[Comparator 2 GP % (Efficiency)]))
I need to subtract the new business formula from my efficiency formulas, as I do not want new business included within those rates. Is this possible given one is iterative and one is not? I cannot flag if a customer is new business as a column, as it'll be based on a users selection from the date table, so needs to be dynamic.
Any help would be really appreciated
