cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlejandroPCar
Helper IV
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

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

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi @Greg_Deckler

 

Here a sample of my tables. Data TableData Table

 

Date TableDate 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.  

 

 

 

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

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. 

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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