Forum Discussion
Rolling Average Time intelligence error
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?
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(?)
6 Replies
Hi,
I can help you if you share the download link of your file. Please also show the expected result.
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...
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(?)
