Calculate Ave Values in same Column by Date

[RandyR1963]

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%

[amitchandak]

@RandyR1963 , For WOW, please refer by blog, How to use Rank to get last week's value.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[kentyler]

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]

@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]

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. 

[RandyR1963]

@kentyler , I am so sorry to cause so much confusion, in which i believe is on my part.. 

Lets see if i understood you correctly 

Created a Measure with 

CurrentWeek = VAR Current_Week = Max('Week/Week Data (2)'[Report Date])

RETURN

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

Current_Week <= Max('Week/Week Data (2)'[Report Date]) ),

[SumTotal61>180])  This does return the Current Week Value,  See Attached

I Also tried with Current_Week added as VAR this way .. 

PrevWeek =

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

VAR Current_Week = Max('Week/Week Data (2)'[Report Date])

RETURN

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

Prev_Week <= Current_Week),

[SumTotal61>180])  Still get the error message as previously sent. 

[kentyler]

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

Prev_Week <= Current_Week),

[SumTotal61>180])  
So our next question is what's happening in the measure [SumTotal61>180], does it return a single value... or a set of rows ?

[kentyler]

if you do

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

'Week/Week Data (2)'[Report Date]<= Current_Week),

[SumTotal61>180])  

you should get the average up to the current week

or 

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

'Week/Week Data (2)'[Report Date]<= Prev_Week),

you should get the average up to the previous week

I admit, i'm getting a little lost in the problem. 

[SumTotal61>180])  

[RandyR1963]

@kentyler   .. I'm with you Sir.   I really appreciate your dilligence in assisting with this..  as we were working through this, i was able to find an alternative to the issue and get the results i was looking for. 

WeekAVG =

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

'Week/Week Data (2)'[Report Date] <= Max('Week/Week Data (2)'[Report Date]) ),

'Week/Week Data (2)'[SumTotal61>180] )

Last Week = 

LW 61>180 = CALCULATE([SumTotal61>180], DATEADD( 'Week/Week Data (2)'[Report Date], -7,DAY)) 

Drops the Week AVG down one week, i know this was what you were getting to

Last Week Average

LWWeekAVG =

AVERAGEX(

Filter ( ALLSELECTED( 'Week/Week Data (2)'[Report Date]),

'Week/Week Data (2)'[Report Date] <= Max('Week/Week Data (2)'[Report Date]) ),

'Week/Week Data (2)'[LW 61>180] )

VAR 61>180=

Var61>180 = [WeekAVG] - [LWWeekAVG]

%VAR61>180=

%VAR61>180 = [Var61>180] / [LWWeekAVG]

Final Result is what i was looking for. 

[RandyR1963]

@kentyler 

So our next question is what's happening in the measure [SumTotal61>180], does it return a single value... or a set of rows ? 

SumTotal61>180 = Sum('Week/Week Data (2)'[Total 61>180])

When i drop this into a Table, it returns one value, 

when i add the Report date to the Table, it gives me the sum for each Report Date