Forum Discussion

rasika_pawar's avatar
rasika_pawar
Frequent Visitor
1 year ago
Solved

Need help in DAX

We are creating a measure where we are calculating one logic which is  Win of Benchmark + Organic of Benchmark - Organic of Survey where, Benchmark is last year date  Survey is current year date ...
  • freginier's avatar
    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_Survey

     

    This will hopefully do everything you want. 

     

    Cheerio,

    Z😁😁