Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Rolling Average visualisation issue Column Chart

 

Hi All,

 

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 

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.PNG

 

But When I amplotting 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.

 

Take a look. 

Graph.PNG

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Write this measure and drag it to your visual

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi I noticed difference which i have to avoid somehow. It is between the table and the graph visualisation of data. 

 

Please take a look at my data model: 

 

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

 

To calculate my Product 3Mth Average I am using the dax below

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

 

It is showing the right numbers when i will place it in the table. 

Table.PNG

 

 

BUT

 

When I am placing it on the graph it is showing it in this way

Graph

How Can I change the formula to present it in the right way. Now it is taking the difference between current month and previous one and divide by 3 which is not what i wanted to do.  Please help

Ashish_Mathur
Super User
Super User

Hi,

 

Write this measure and drag it to your visual

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

I'd like to suggest you use below measure formula if it suitable for your requirement:

Product3Mth =
VAR currDate =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Product A uncum] );
        FILTER (
            ALLSELECTED ( 'Table' );
            'Table'[Date]
                >= DATE ( YEAR ( currDate ); MONTH ( currDate ) - 3; DAY ( currDate ) )
                && 'Table'[Date] <= currDate
        )
    )
        / 3

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.