Need help with date slicer working on quick measures (rolling averages)

[vkirchho]

Newbie here - I have several rolling averages created as quick measures. These averages are trailing 7 day rolling averages over a multi-year time period, and are based on underlying daily data. I want to use a slicer that will allow my visuals to only show the rolling averages over some date range, for example November of 2019 thru March of 2020. I have a date hierarchy for my relevant date field. However, when I use the slicer with my date field, I get an display error saying 'Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierchy or primary date column'. But when I try to use the date hierarchy instead of the date field in the basic slicer, it only allows my to filter by year (not nearly granular enough).

I also tried the timeline slicer (version 2.1.1) but the hierarchy doesn't work at all with this slicer - the data remains unchanged and the slicer reverts back to all time periods after I try filtering with it. Is there a slicer that will let me filter my rolling averages the way I want?

[Icey]

Hi @vkirchho ,

If there are no other questions, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[Icey · Accepted solution]

Hi @vkirchho ,

---

---

@mattbrice explained the cause in this post:

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] ) so the calculation does not return an incorrect result.

The quick measures feature only works on very simple data models ( a single table). Doesn't scale to well.

The easiest method is to create the moving average measure yourself using DAX. No limitations that way. Quick measure is just a way to generate DAX code through the GUI.

See this guide: https://www.daxpatterns.com/statistical-patterns/#moving-average 

or https://powerpivotpro.com/2013/07/moving-averages-sums-etc/

Reference: Quick Measure Error: “Only Power BI-provided date hierarchies are supported”.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vkirchho]

Thanks Icey. I will dig into the materials you referenced and see if I can't get to a solution. Agree solving with DAX code seems to be the best way to arrive at a solution.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[vkirchho]

Greg, thank you for your response! If I got the gist of your solution, you use some DAX code to solve the problem. The examples you have seem to involve setting up a temporary table to hold calculations, which seems like a pretty ingenious solution. I'm going to have knock around a bit and see if this can't be used to get where I want to go.

Thanks again for the help!

[Greg_Deckler]

That's the gist of it. Basically, if you think about it, what time intelligence functions are doing is not really all that intelligent, they just return a set of dates from a date table. Well, you can do that with just a FILTER function and then you have complete control over exactly what is returned and not returned as in what is included and not included. So, why not just do that and skip all the black box magic with the Time Intelligence functions? Then you know what is going on versus simply trusting that you are using these black boxes correctly.

Personally, I wish the time intelligence functions in DAX would be retired or have never been invented in the first place. They have caused endless hardship for people and they can all be replaced by pretty simple DAX patterns. I am not a fan of them. I personally find the dumb, unintuitive and generally useless. But that's just my opinion.