Calculate Average growth between two dates in different tables

[AlejandroPCar]

Hi!

Im using AVERAGEX for calculate average anual growth for my measure "Prices". But I have two dates tables (Time A and Time B )with its respective dates columns. These two tables are the same. So meanwhile AVERAGEX only allows me one table as reference, how can I an do a measure that caculates the average anual growth between start date (Time A ) and end date (Time B ). 

Im currently using my formula AAG =

AAG = AVERAGEX(  ALLSELECTED(  'Time A'  ),  [AG]  ) 

AG is my anual growth measure. That is the average anual growth only for Time A table. The allselected is for a line graph purpose. I want to to the same line but now selcting two dates with my two tables.  

I have both tables with desactivated relationships with my data table ( Prices Table ). 

Hope you can help me. Thank you a lot. 

[Greg_Deckler]

Sample data would help tremendously. I'm thinking that you need to use VAR and something like maybe a UNION or SUMMARIZE or something to create a temp table to use in your AVERAGEX, but I can't be sure really.

[AlejandroPCar]

Hi @Greg_Deckler

Here a sample of my tables. Data Table

Date Table

Both Date tables have the same structure. The idea is I choose i.e. in Table A I select November 2017 and in Table B I select January 2015, I can see the average anual growth for all the months between those dates.  

[v-huizhn-msft · Accepted solution]

Hi @AlejandroPCar,

We can not select one value from one table. You should create two slicers, one slicer including TableA[Date.Month], another slicer including TableB[Date.Month]. Note: there is no relationship between TableA and TableB. Then create a measure using the formula below.

AAG =
VAR start_date =
    SELECTEDVALUE ( TableA[Date.month] )
VAR end_date =
    SELECTEDVALUE ( TableB[Date.month] )
RETURN
    AVERAGEX (
        FILTER ( TableA, Table[month] <= [end_date] && Table[month] >= [start_date] ),
        [AG]
    )

Please feel free to ask if you have other issue.

Best Regards,
Angelia

[AlejandroPCar]

Hi! @v-huizhn-msft

Thanks for your response. Your solution seems helpful and Im using it for practice. But my solution was create  a third Date Table and use the Averagex there. So, instead select an Start Date in Table A and and End Date in Table B for my graph I just select both dates for separate in the graph in each Table and the calculate the average with the C Table selecting the previous dates. 

But, thank for your help, your solution is more elegant.