Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Comparing averages to a baseline date

Hello everyone! I want to take some averages that come from a baseline date, then take averages from all other dates and subtract the second one from the first. I have created a formula that is sh...
Need Help
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

it depends on your data model, but from a first sight you dont need any filter in current calculation

AET Difference with Baseline =
VAR baselineAverage = CALCULATE([Average Event Time], FILTER(ALL('Runtime Info'), [Date] = DATE(2020, 04, 03)))
VAR currentAverage = CALCULATE(AVERAGE([Average Event Time]))

RETURN
baselineAverage - currentAverage

does [Average Event Time] column or measure?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38  So I replaced the formula with the one you sent me and I still didn't get the correct results. The 3rd of April is the base line so I suppose the AET Difference with Baseline should me 0. Bellow are the results I get. Any thoughts?

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    what the statement do you use for [Average Event Time]  measure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      az38  I use this one

       

      Average Event Time = AVERAGE('Runtime Info'[Event Time])
      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

        ypur statement looks good enough

        didn't your try to debug? whats component is wrong? baselineAverage  or currentAverage?

        also, try this

        AET Difference with Baseline =
VAR baselineAverage = CALCULATE(AVERAGEX(FILTER(ALL('Runtime Info'), [Date] = DATE(2020, 04, 03)), [Event Time])
VAR currentAverage = CALCULATE(AVERAGE([Event Time]))

RETURN
baselineAverage - currentAverage