Check if a value is decreasing over a period of time.

[Anonymous]

Hi all,

This is regarding a performance trend report. Please feel free to share ideas. I have a rolling/moving average measur for my numreric data  for trend analytics and to show the trend, I am using both inbuilt and custom visuals. I want to take it to next level and build something that shows below;

"Over a period of time, if this rolling/moving average is decreasing/increasing", then I would like to show that specific category. The challenge here is to dynamically identify the category for which the rolling average is decreasing/increasing over a period of time(lets say 3 months). Manually I can easily do analysis on charts and figure out which one is decreasing/increasing. 

[danextian]

Hi @Anonymous ,

Try this approach. Create these measures for the moving averages this month and last month

Rolling Average CM = 
VAR __LAST_DATE =
    LASTDATE ( 'Table'[Date] )
VAR __DATE_PERIOD =
    DATESBETWEEN (
        'Table'[Date].[Date],
        STARTOFMONTH ( DATEADD ( __LAST_DATE, -2, MONTH ) ),
        __LAST_DATE
    )
RETURN
    IF (
        __LAST_DATE <> BLANK (),
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE (
                    VALUES ( 'Table' ),
                    'Table'[Date].[Year],
                    'Table'[Date].[QuarterNo],
                    'Table'[Date].[Quarter],
                    'Table'[Date].[MonthNo],
                    'Table'[Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE ( [Sum of Value] )
        )
    )

Rolling Average LM = 
VAR __LAST_DATE =
    DATEADD ( LASTDATE ( 'Table'[Date] ), -1, MONTH )
VAR __DATE_PERIOD =
    DATESBETWEEN (
        'Table'[Date].[Date],
        STARTOFMONTH ( DATEADD ( __LAST_DATE, -2, MONTH ) ),
        EOMONTH ( __LAST_DATE, 0 )
    )
RETURN
    IF (
        __LAST_DATE <> BLANK (),
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE (
                    VALUES ( 'Table' ),
                    'Table'[Date].[Year],
                    'Table'[Date].[QuarterNo],
                    'Table'[Date].[Quarter],
                    'Table'[Date].[MonthNo],
                    'Table'[Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE ( [Sum of Value] )
        )
    )



​

Create another measure  to compute for the difference betweent the two measures above:

Rolling Average MoM Difference = 
[Rolling Average CM] - [Rolling Average LM]​

Use this measure in your visual. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Waterfall charts shows increase and decrease trends.

https://radacad.com/power-bi-waterfall-chart-whats-that-all-about

You can tell Power BI to explain increses or decreases in charts as below:

https://docs.microsoft.com/en-us/power-bi/desktop-insights

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490