cancel
Showing results for
Did you mean:
Helper IV

## Calculate Average growth between two dates in different tables

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.

1 ACCEPTED SOLUTION
Microsoft

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

Best Regards,
Angelia

4 REPLIES 4
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Helper IV

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.

Microsoft

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

Best Regards,
Angelia

Helper IV

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.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors