Moving Average Not Behaving as Desired

[Anonymous]

I have a 7 day moving average measure that works fine except for one thing, it stops as of the current date.  Here's my measure:

Addition $: 7d MA =
VAR __LAST_DATE =
    LASTDATE ( 'Calendar'[Date] )
RETURN
    IF (
        __LAST_DATE <= [Last Refresh Time:],
        AVERAGEX (
            DATESBETWEEN (
                'Calendar'[Date],
                DATEADD ( __LAST_DATE, -7, DAY ),
                __LAST_DATE
            ),
            CALCULATE ( [Addition $] )
        ),
        BLANK ()
    )

Blank overall amount

Everything in the future, including the 'total' is blank.  Now, I know that this is what I told it to do in the false part of the IF, but I cannot figure out how to make that same AVERAGEX calculation work for future dates.  I tried putting in another variable and using it in the DATESBETWEEN but it gives garbage results.

To see what is happening I used a COUNTX instead.  Here's revised DAX and results:

Addition $: 7d MA =

VAR __LAST_DATE =

    LASTDATE ('Calendar'[Date])

VAR __NOW =

    LASTDATE('Last Refresh Time'[LocalTime]) /* Basically a single row table */

RETURN

IF (

    __LAST_DATE <= [Last Refresh Time:],

    AVERAGEX (

        DATESBETWEEN (

            'Calendar'[Date],

            DATEADD ( __LAST_DATE, -7, DAY ),

           __LAST_DATE

        ),

        CALCULATE ( [Addition $] )

    ),

    COUNTX (

        DATESBETWEEN (

            'Calendar'[Date],

            DATEADD ( __NOW, -7, DAY ),

            __NOW

        ),

        CALCULATE ( [Addition $] )

    )

)

With COUNTX

There are not 952 in DATEDBETWEEN 7 days ago and today!  I would expect to see 7 there 

[Anonymous]

DATEADD is a time-intel function and works correctly on a column of dates in a proper calendar table. You can't have DATEADD ( __NOW, -7, DAY ), because __NOW belongs to a different table, not to Calendar.

You can check it: DATEADD ( __NOW, -7, DAY ) = BLANK().

Best
D

[Anonymous]

That makes sense now. 

Do you have any suggestions how I can create a __NOW variable that will get LASTDATE('Calendar'[Date]) properly filtered to stop at TODAY()?

[Anonymous]

var __now =
	CALCULATE(
		VALUES( 'Calendar'[Date] ),
		TREATAS(
			LASTDATE('Last Refresh Time'[LocalTime]),
			'Calendar'[Date]
		)
	)	

Best

D

[Anonymous · Accepted solution]

Much appreciated, darlove.  While that didn't fix my issue, you exposed something to me that allowed me to fix it myself.

When I used your __NOW variable, the DATEADD function in the IF(false) statemet barked at me.  That's because a CALCULATE doesn't yield a table and DATEADD wants a table.

But, by playing around a bit I learned a couple of things.  First, my [LocalTime] column is a datetime.  Evidently DATEADD didn't like having a datetime field passed to it for some reason.  Anyway, I opted to add a [Date] field to 'Last Refresh Time' table through the back end.  I then played around a bit to finally come up with this working variable:

VAR __NOW =

    TREATAS(LASTDATE('Last Refresh Time'[Date]), 'Calendar'[Date])

Thanks.  I coundn't have done it without the TREATAS tip!

[Anonymous]

Glad it helped.

"DAX is simple but it's not easy." - Marco Russo and Alberto Ferrari, the DAX gurus.

Best
D