Forum Discussion

RandyR1963's avatar
RandyR1963
Frequent Visitor
6 years ago

Calculate Ave Values in same Column by Date

Hi Experts, 

 

Looking for help with a measure to calculate the difference in Column Values by Date, 

I have created a Measure that gives me the average of the sum of a total clolumn by date .. 

This is the measure > 

AVERAGEX( Filter ( ALLSELECTED( 'Week/Week Data'[Report Date]), 'Week/Week Data'[Report Date] <= Max('Week/Week Data'[Report Date]) ), [Total 61>180] ).. Results are exactly what i need. 
 
I would now like to Calulate the difference in this Clolumn (Avg Total)  by Report Date showing the reduction or addition week over week.
Requested Result > 04/11 Date /Avg Total - 04/04 Date/Avg Total = Requested Value 
Requested %  > 04/11 Date/ Requested Value / 04/04 Date/Avg Total = % Reuction 
 
I have searched vigeriously through all the Community Help topincs and i am not finding a suitable solution to a measure calulation covering this sinereo 
 
Any help is greatly appreciated. 
 
Sample Table 
Report Date     Avg Total      Requested Value    Requested %
04/04/2020      745227          0 or 745227            0
04/11/2020      662814          -82,413                   -11.1%
04/18/2020      534561         -128252                   - 19.9%
04/25/2020      452591         -81969                     -15.3%
Need Help

9 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    AVERAGEX(
        Filter ( ALLSELECTED( 'Week/Week Data'[Report Date]),
                'Week/Week Data'[Report Date] <= Max('Week/Week Data'[Report Date]) ), 
       [Total 61>180] )
    to solve your problem you have to change this date in your FILTER to be either one week ahead of the date in the current row, or one week behind it.
    setup 2 VAR statements before you call AVERAGEX
    VAR Prev_Week = DATEADD('Week/Week Data'[Report Date],-7,DAY)

    and
    VAR Next_Week = DATEADD('Week/Week Data'[Report Date],+7,DAY)

    then 2 VAR statements to store the result of calling AVERAGEX with the 2 dates

    that will give you 2 VARs you can use to calculate the difference.

    • RandyR1963's avatar
      RandyR1963
      Frequent Visitor
      6 years ago

      kentyler , Thank you for assisting, I think i am getting a little lost in your discription, 

       

      This is how i have interpreted how you have discribed, 

      My Original Measure was 

       

      AVERAGEX(
          Filter ( ALLSELECTED( 'Week/Week Data'[Report Date]),
                  'Week/Week Data'[Report Date] <= Max('Week/Week Data'[Report Date]) ), 
         [Total 61>180] )  I Created a Copy of the Original Data set so i didn't mess it up, some value names changed. 

       

      With the 2 VARs below, i am assuming to replace the BOLD section in original measure with either Next_Week or Prev_Week as in the New measures below 

       

      "setup 2 VAR statements before you call AVERAGEX"


      VAR Prev_Week = DATEADD('Week/Week Data'[Report Date],-7,DAY)

       

      My Measure > 

      PrevWeek = VAR Prev_Week = DATEADD('Week/Week Data (2)'[Report Date], -7, DAY)
      RETURN
      AVERAGEX(
      Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
      Prev_Week <= Max('Week/Week Data (2)'[Report Date]) ),
      [SumTotal61>180])  This returns ERROR, Calc error in measure, [Next_Week}: A table of multiple Values was supplied where a single value was expected

       

      and
      VAR Next_Week = DATEADD('Week/Week Data'[Report Date],+7,DAY)

      My Measure > 

      NextWeek = VAR Next_Week = DATEADD('Week/Week Data (2)'[Report Date], +7, DAY)
      RETURN
      AVERAGEX(
      Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
      Next_Week <= Max('Week/Week Data (2)'[Report Date]) ),
      [SumTotal61>180])    This returns no results.. Same Error as above. 
       
      Then you stated "then 2 VAR statements to store the result of calling AVERAGEX with the 2 dates" 
      Which is my assumption these Measuers would be something like, 
      NextWeek = Sum(Next_Week) 
      and
      Prev_Week = Sum(Prev_Week),   
      Then being able to do a measure called  Variance = NextWeek-PrevWeek to get teh variance i am looking for . 
       
      Now, the dates in my table is of a report that i have duplicated and Grouped the totals by the Report Date, could this be some of or all of the issue with the Error message? , 
       
      Thank you in advance for any guidence. 
      Randy
      • kentyler's avatar
        kentyler
        Icon for Solution Sage rankSolution Sage
        6 years ago

        My Measure > 

        PrevWeek = VAR Prev_Week = DATEADD('Week/Week Data (2)'[Report Date], -7, DAY)
        RETURN
        AVERAGEX(
        Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),
        Prev_Week <= Max('Week/Week Data (2)'[Report Date]) ),
        [SumTotal61>180])  This returns ERROR, Calc error in measure, [Next_Week}: A table of multiple Values was supplied where a single value was expected
         
        Add another var
        VAR current_week = MAX( 'Week/Week Data (2)'[Report Date])
        then temporarily have the measure return that VAR... see if it returns what you think it should
        you can do the same thing in the measure that is trying to return the next week.