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😁😁
Hi rasika_pawar,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.
Hi rasika_pawar,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.