Date Filter with Overlapping Preselects (Today, Yesterday, Last 7 Days, Last 30 Days, Last Month...)

[danzrust]

I am looking for hints on how to replicate these filter preselects in Power BI:

I want to to simply create this as a dropdown filter with no other extra steps for my users. Assume my users are 100% lame and they can only understand a simple drodown. 

I was searching for a solution but I could not find any. I am very suprised I could not find anything because this is a very common request from business users.

My data model has a standalone calendar table. 

Any ideas? Is this even possible?

[danzrust]

@mahoneypat 
Okay, I get the idea and it's pretty slick. But I need a little push on the DAX side of things. 

My use case is that I just need to use the calculation group items as filter option in a slicer. I have an existing calendar table which tells me what date is e.g. "yesterday", "last week", "this month", "last month" etc. 

I've created my group but it "does not work" (pretty sure, you hear this quote quite often).

Here is my situation:

All the other calculation items are created in a similar fashion.

You can see that I'm using the group item in the slicer, yet the table with my performance stats does not get filtered based on my slicer selection.

I've noticed that only calculated measures (which I also have in my model) would get filtered in my table based on the selection in the slicer, not the ones which are coming straight from the raw data. In my example, the "Impr" in the table is coming straight from the raw data. It's not a "measure" created via DAX. I am just downloading data from BigQuery via Query Editor.

Any hints? 🙂
 

[mahoneypat]

In your calculation item expression try using this instead

CALCULATE(SELECTEDMEASURE(), ALL('Calendar'), 'Calendar'[Last X Weeks] = "Current week")

Pat

[danzrust]

Hi Pat,
I've gone ahead and created standalone aggregated metrics so I could use them in the calculation groups. There are some other indirect benefits of having these agg metrics ready to go anyway 🙂

Thanks for your help!

[mahoneypat]

You can do that with a Calculation Group. Just create a calculation item for each pre-set date range you want, and then you can use the calculation item name column in your slicer. You will need Tabular Editor to create it, but there is a free version if you are not already using it.

Creating Calculation Groups in Power BI Desktop - SQLBI

Pat