How to control axis when using a moving average?

[CloudMonkey]

Hi,

Please can you tell me how to control the axis of a graph when using a 3-day moving average? My problem here is that at the start of the date range the first two dates are a 1 and 2 day average respectively (highlighted). And at the end of the date range the last two dates are two and one day averages respectively (also highlighted). Please find the pbix file here -> https://filebin.net/apmzbk8wwjvi16fh

Thank you for your help,

CM

[CNENFRNL · Accepted solution]

Hi, @CloudMonkey , Here Autodate messed things up, like always; that's why I barely use this feature. A measure can be authored like this WITHOUT autodate,

Volume rolling average (no Autodate) =
VAR __LAST_DATE =
    LASTDATE ( 'Sales'[Date] )
VAR __DATE_RNG =
    DATESBETWEEN ( 'Sales'[Date], DATEADD ( __LAST_DATE, -2, DAY ), __LAST_DATE )
RETURN
    IF (
        COUNTROWS ( __DATE_RNG ) = 3,
        AVERAGEX ( __DATE_RNG, CALCULATE ( SUM ( 'Sales'[Volume] ) ) )
    )

[Icey]

Hi @CloudMonkey ,

Please let us know whether @CNENFRNL 's answer is what you want.

Best Regards,

Icey

[amitchandak]

@CloudMonkey , Try like

Rolling 3 day =
var _max = minx(allselected(Date),[Date[Date])
var _min = minx(allselected(Date),[Date[Date])
return
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day) filter(Date,'Date'[Date] <=_min && 'Date'[Date]>=_max)

or

CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))

[CNENFRNL · Accepted solution]

If you intend to keep Autodate with some other measures depending on it, this measure can be changed like this to remove those 4 highlighted values in the viz,

Volume rolling average =
IF (
    ISFILTERED ( 'Sales'[Date] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __LAST_DATE =
        LASTDATE ( 'Sales'[Date].[Date] )
    VAR __DATE_RNG =
        DATESBETWEEN (
            'Sales'[Date].[Date],
            DATEADD ( __LAST_DATE, -2, DAY ),
            __LAST_DATE
        )
    RETURN
        IF (
            COUNTROWS ( __DATE_RNG ) = 3
                && CALCULATE ( MAX ( Sales[Volume] ), __LAST_DATE ) > 0,
            AVERAGEX ( __DATE_RNG, CALCULATE ( SUM ( 'Sales'[Volume] ) ) )
        )
)

[CNENFRNL · Accepted solution]

Hi, @CloudMonkey , Here Autodate messed things up, like always; that's why I barely use this feature. A measure can be authored like this WITHOUT autodate,

Volume rolling average (no Autodate) =
VAR __LAST_DATE =
    LASTDATE ( 'Sales'[Date] )
VAR __DATE_RNG =
    DATESBETWEEN ( 'Sales'[Date], DATEADD ( __LAST_DATE, -2, DAY ), __LAST_DATE )
RETURN
    IF (
        COUNTROWS ( __DATE_RNG ) = 3,
        AVERAGEX ( __DATE_RNG, CALCULATE ( SUM ( 'Sales'[Volume] ) ) )
    )

[CloudMonkey]

Thank you CNENFRNL. It was autodate that was the problem. I coudn't quite understand averagex (a battle for another day!) so I used the formula below but it seems to be working. Thanks

Volume (3 day trailing total) (auto number of days) = 
VAR
    CurrentDate = max(Sales[Date])
VAR
    Date2DaysAgo = CurrentDate - 2
VAR
    DaysInMovingAverage = CurrentDate - Date2DaysAgo + 1
Return
    CALCULATE(sum(Sales[Volume]),filter(All(Sales[Date]),
        Sales[Date] >= Date2DaysAgo &&
        Sales[Date] <= CurrentDate
    )) / DaysInMovingAverage