Forum Discussion
Rolling Average Time intelligence error
- 8 years ago
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(?)
Hi,
I can help you if you share the download link of your file. Please also show the expected result.
- RobertSlattery8 years agoResponsive Resident
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...
- mattbrice8 years agoSolution Sage
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(?)
- RobertSlattery8 years agoResponsive Resident
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.
- avcr298 years agoFrequent Visitor
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,
- avcr298 years agoFrequent Visitor
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,