Find Quarterly average event though the context of the table is daily

[Puthenveedu]

Hi,

I need my measure to calculate the average of the fullQuarter  eventhough i am displaying the measure for every date of the month.

I have contract sold on every day of the month and when I display the daily row data I want to also display the average of the corresponding quarter . eg Contract 'ABC' sold on the Feb 15th shows as say $500 , and let the average of the corresponding Quarter is $450. I created a measure with 'AllExcept ' so it will keep the context of the measure still in the quarter level event thought my measure needs to be displayed on a daily row basis.

Date                |     Contract ID            |       Sold Price            |          QuarterlyAverage

02/15/2019     |        ABC                    |         $500                  |          $450     

02/16/2019     |        JKL                      |         $200                  |          $450         

02/17/2019     |        POI                     |         $250                  |          $450             

_mQuarterlyAverage =CALCULATE( AVERAGE( TBL[Sold Price])
,ALLEXCEPT(TBL,'Date'[YearQuarter])
,TBL[statuscodename]= "Closed"
,TBL[StatecodeName]="Active"

)

YearQuarter represent the corresponding quarter of each date eg:2018 Q1, 2018 Q2 etc.

my measure keep calculating average for the full available year as oppose to the corresponding quarter.

please help me find what i am doing wrong. Thanks, JS

[Jimmy801]

Hello

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello @Puthenveedu 

Try to use this measure. This should do the trick

Average Quarter = 
var EOQuarter = ENDOFQUARTER(TBL[Date]) 
var SOQuarter = STARTOFQUARTER(TBL[Date]) 
return
CALCULATE(
    AVERAGE(TBL[Sold Price]);
    filter(
        all(TBL);
        TBL[Date]>=SOQuarter&&TBL[Date]<=EOQuarter
    )
)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi, 

Is this maybe a solution: 

Created a 2 measures: 

1: AVG Price = AVERAGEX(ContractSales, ContractSales[Sold Price])

2:  M5 =

VAR QuarterAtLine = MAX ( 'Date'[Calendar Quarter] )

VAR Result =  SUMX (

ADDCOLUMNS (

SUMMARIZE ( ContractSales, 'Date'[Calendar Quarter] ),

"Average Sold Price",

CALCULATE ( [AVG Price],

ALL (),

'ContractSales'[Status] in {"Open", "Active"},

'Date'[Calendar Quarter] = QuarterAtLine

)

),

[Average Sold Price]

)

RETURN

Result

For me its works in both a Matrix and a Table

 Hopre this works for you too.

Regards,

Jan 

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

In short, you will need a measure that probably involves an ALL in order to snap it out of the current monthly context and then you would FILTER that down to just the quarter you are in.