Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
Here a sample of my tables.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |