Forum Discussion
Need help in DAX
- 1 year ago
Hey there!
So if I understood, you want a DAX measure that calculates Win of Benchmark + Organic of Benchmark - Organic of Survey, while ensuring it works with the Survey Period and Benchmark Period slicers.
Maybe try thisone:
Win_Organic_Calculation =
VAR SurveyYear = SELECTEDVALUE(SurveyTable[Year]) -- Get selected Survey Year
VAR BenchmarkYear = SurveyYear - 1 -- Benchmark Year is Survey Year - 1-- Win of Benchmark: Contracts created in Benchmark Year
VAR Win_Benchmark =
CALCULATE(
COUNTROWS(ContractsTable),
ContractsTable[CreationYear] = BenchmarkYear
)-- Organic of Benchmark: Contracts active in Benchmark Year that were renewed from two years ago
VAR Organic_Benchmark =
CALCULATE(
COUNTROWS(ContractsTable),
ContractsTable[StartYear] = BenchmarkYear - 1,
ContractsTable[RenewalYear] = BenchmarkYear
)-- Organic of Survey: Contracts active in Survey Year that were renewed from Benchmark Year
VAR Organic_Survey =
CALCULATE(
COUNTROWS(ContractsTable),
ContractsTable[StartYear] = BenchmarkYear,
ContractsTable[RenewalYear] = SurveyYear
)-- Final Calculation
RETURN Win_Benchmark + Organic_Benchmark - Organic_SurveyThis will hopefully do everything you want.
Cheerio,
Zππ
Hey there!
So if I understood, you want a DAX measure that calculates Win of Benchmark + Organic of Benchmark - Organic of Survey, while ensuring it works with the Survey Period and Benchmark Period slicers.
Maybe try thisone:
Win_Organic_Calculation =
VAR SurveyYear = SELECTEDVALUE(SurveyTable[Year]) -- Get selected Survey Year
VAR BenchmarkYear = SurveyYear - 1 -- Benchmark Year is Survey Year - 1
-- Win of Benchmark: Contracts created in Benchmark Year
VAR Win_Benchmark =
CALCULATE(
COUNTROWS(ContractsTable),
ContractsTable[CreationYear] = BenchmarkYear
)
-- Organic of Benchmark: Contracts active in Benchmark Year that were renewed from two years ago
VAR Organic_Benchmark =
CALCULATE(
COUNTROWS(ContractsTable),
ContractsTable[StartYear] = BenchmarkYear - 1,
ContractsTable[RenewalYear] = BenchmarkYear
)
-- Organic of Survey: Contracts active in Survey Year that were renewed from Benchmark Year
VAR Organic_Survey =
CALCULATE(
COUNTROWS(ContractsTable),
ContractsTable[StartYear] = BenchmarkYear,
ContractsTable[RenewalYear] = SurveyYear
)
-- Final Calculation
RETURN Win_Benchmark + Organic_Benchmark - Organic_Survey
This will hopefully do everything you want.
Cheerio,
Zππ