Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
8 years ago
Solved

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. 

 

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      AlejandroPCar
      Icon for Helper IV rankHelper IV
      8 years ago

      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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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