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 shown bellow but it doesn't seem to work. I have it on a matrix visualization where each column is a date.

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

RETURN
baselineAverage - currentAverage
 
Any help would be appreciated. Thank you!
Need Help

19 Replies

  • az38's avatar
    az38
    Community 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

      Hi az38 

      It's a 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
        Community Champion
        6 years ago

        Anonymous 

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