Rolling Average Time intelligence error

[RobertSlattery]

When I use Quick Measures to create a Rolling Average it generates the following DAX...

IF(
	ISFILTERED('Invoice_Summary All'[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('Dim Date'[Date].[Date])
	RETURN
		AVERAGEX(
			DATESBETWEEN(
				'Invoice_Summary All'[Date],
				DATEADD(__LAST_DATE, -6, DAY),
				__LAST_DATE
			),
			CALCULATE(SUM('Invoice_Summary All'[Sales Per Line]))
		)
)

I Date slicer on the page where I select the Last 12 Months.  Why does this throw if there is any filter on the date? I am filtering by the primary date column, so why is that a problem for the Time intelligence function?

[Ashish_Mathur]

Hi,

I can help you if you share the download link of your file.  Please also show the expected result.

[RobertSlattery]

Thanks, but I did it a diferent way to avoid the problem.

Can you help me to unserstand why having a filter on the date breakse the DATESBETWEEN function in this context?  To me, this makes it pretty much useless...

[avcr29]

Hello,

I just had the same error but i don't know how can avoid this problem, if i remove the validation, it doesn't show the error but it does the wrong calculation, can you explain or show how you solved it?

Thanks,

[avcr29]

Hello,

I just had the same error but i don't know how can avoid this problem, if i remove the validation, it doesn't show the error but it does the wrong calculation, can you explain or show how you solved it?

Thanks,

[mattbrice · Accepted solution]

It's because Power BI tries to help people by automatically generating hidden calendar tables that are related to Date columns in the model.  These hidden but separate Date tables are accessed using the dot notation like "'Dim Date'[Date].[Date]" and are required for some Power BI generated Quick Measures.  The quick measure is checking to make sure the user doesn't put a filter on their user created, non-hidden Date column ( 'Dim Date'[Date] in your case ) so the calculation does not return an incorrect result. I always create my own Calendar tables and turn off these hidden calendar tables so not sure if there is a workaround.  Of course, you could also just modify the Quick Measure Dax to remove the dot notation like this: 

Measure =
VAR __LAST_DATE =
    LASTDATE ( 'Dim Date'[Date] )
RETURN
    AVERAGEX (
        DATESBETWEEN (
            'Invoice_Summary All'[Date],
            DATEADD ( __LAST_DATE, -6, DAY ),
            __LAST_DATE
        ),
        CALCULATE ( SUM ( 'Invoice_Summary All'[Sales Per Line] ) )
    )

Quick Measures are a good way to learn DAX coding techniques.

Now that Power BI has the ability to "Mark as Date Table" like in excel, perhaps the Quick Meaures will be updated to use user created Calendar tables(?)

[RobertSlattery]

Thanks, that is exactly the kind of answer I was looking for, Much apreciated.

I have the Jan version of PBI and can't see the Mark as Date Table option so I can't try it out.  One problem I have with your advice is that I can't use Time Intelligence functions requiring dot notation if I disable Auto Date/Time, so I can't access the hierarchy.  Hopefully this will be fixed with the latest version.