I'm new to DAX and Power Query. For days I've been having troubles with what should be a simple calculation. I need a solution for this problem, either using DAX or Power query or both.
I prepared the following example dataset to explain my case:
As you can see, I have a date column which contains date values of the 1st day of every 3 months (representing year quarters). The second column contains integer values.
I need to get the interannual variation of those values, and then, the average over those results.
Calculating interannual variation is pretty simple, I just made a calculated measure with the following formula:
int_variation = IF(
ISBLANK(DIVIDE(SUM(Data[Value]),CALCULATE(SUM(Data[Value]),SAMEPERIODLASTYEAR(Data[Quarter])),BLANK())),
BLANK(),
(DIVIDE(SUM(Data[Value]),CALCULATE(SUM(Data[Value]),SAMEPERIODLASTYEAR(Data[Quarter])))-1)
)
Getting the following results:
Now, the problem comes when I try to get the average of those calculated interannual variations.
I've tried using AVERAGEX DAX function like this:
avg_int_variation = AVERAGEX(Data,[int_variation])
Also tried adding VALUES function as suggested here:
avg_int_variation = AVERAGEX(VALUES(Data[Value]),[int_variation])
But returns a blank value in both cases:
Since the query must be dynamic (that means number of quarters may change) is not that easy as querying a sum of the last 4 values and divide the result by 4. What I need is a formula that takes all values in the dataset, calculate the interannual variation of all of those values(I guess that is done so far) and then return the average for those values regardless of how many values are.
Important facts:
• In addition to the average I also need the standard deviation of the interannual variation values.
• I must use only PowerBi.
Example of the final result I'm looking for (done in Excel):
As I said above, a DAX and/or Power Query solutions are viable. Could you make it?
You can download my sample PowerBi report here if you want to use it.
Thanks in advance.
