Power BI Rolling Average DAX to plot correctly on Column Chart

3
votes

I have a problem with the measure of the 3mth rolling average to visualise it correctly on the graph.

The data model is here:

https://docs.google.com/spreadsheets/d/1naChcuZtjSbk0pVEi1xKuTZhSY7Rpabc0OCbmxowQME/edit?usp=sharing

I am using the formula below to calculate 3mth average through a measure:

Product3Mth = CALCULATE(SUM('Table'[Product A uncum]);DATESINPERIOD('Table'[Date];LASTDATE('Table'[Date]);-3;MONTH))/3

When I am plotting it as a table it is showing right values for each month.

Table visualisation

But When I am plotting it in the column chart together with Product A Accumulated I am getting wrong value which is the value for Product unaccum /3 insted of sum of 3 consecutive values for Product unaccum /3.

Column Chart visualization

What should I change in the DAX to have it visualised correctly? Please HELP

3
averagepowerbidax
You are using the formula of Product3Mth as a Measure or Calculated Column? – ptfaferreira

3 Answers

1
votes

It seems you've stumble accross quite a few Power BI "gotchas" here when it comes to both the format of the date in your source data and the way you've chosen to display the Date column in your visulaization. But I think I've figured it out. This is my result:

enter image description here

And just to verify some numbers:

(4043 + 20 + 158) / 3 = 1469

(189+ 200 + 207) / 3 = 199

And here are the details:

I used this dataset where I've changed the names slightly to make it easier to write DAX expressions and imported it using Get Data

Date                unAcc   ACc
01-10-2017 00:00    4043    4043
01-11-2017 00:00     205    4248
01-12-2017 00:00     158    4406
01-01-2018 00:00     142    4548
01-02-2018 00:00     312    4860
01-03-2018 00:00     258    5118
01-04-2018 00:00     176    5294
01-05-2018 00:00     210    5504
01-06-2018 00:00     189    5693
01-07-2018 00:00     200    5893
01-08-2018 00:00     207    6100

And for reasons still uknown to me, I had the same issues as you had with the Date column. But following some tips from the Power BI community, I created a enter image description here Date2 like this :

Date2 = 
DATE('Table1'[Date].[Year];'Table1'[Date].[MonthNo];1)

Then I calculated the three month average using a enter image description here

Moving_Average_3_Months = 
CALCULATE (
    AVERAGEX ( 'Table1'; 'Table1'[unAcc] );
    DATESINPERIOD (
        'Table1'[Date2];
        LASTDATE ( 'Table1'[Date2]);
        -3;
        MONTH
    )
)

Now, if you insert a column chart and assign Date2 to the Axis and Moving_Average_3_months together with unAcc to Values, you'll get this:

enter image description here

And that's not what we want. So go to the Visualization settings and change Date2 from Date Hierarchy to simply Date2 like this:

enter image description here

And that's it:

enter image description here

And here's the whole thing as a table so you can see that the numbers are correct:

enter image description here

In your case, maybe the only thing you have to do is that very last part.

Please don't hesitate to let me know how it works out for you!

0
votes

I ask in the last comment the issue about the calculated value (as column or measure) because I have different results based on this, as you can see in the example below:

enter image description here

The Product3Mth is based on the calculated column and the Product3Mth 2 is based on the measure (result you want!).

Hope this example can help you. If not tell me please!

0
votes

The DATESINPERIOD and LASTDATE functions will not work correctly, because 'Table'[Date] is not a continous range of dates. These functions need to be based on a datetable.

Try something like this

Product3MthAVG =
VAR rowDate = 'Table'[Date]
RETURN
    CALCULATE (
        SUM ( 'Table'[Product A unaccum] );
        FILTER (
            'Table';
            'Table'[Date]
                < DATE ( YEAR ( rowDate ); MONTH ( rowDate ) + 1; DAY ( rowDate ) )
                && 'Table'[Date]
                    >= DATE ( YEAR ( rowDate ); MONTH ( rowDate ) - 2; DAY ( rowDate ) )
        )
    )
        / 3