Using a measure to change data to weekly or monthly date range

[albertax]

Hi, I am wondering if  you are able to help me with a measure where i am wanting people to be able to filter to the last 7 days (weekly) or last 30 days monthly by clicking on a slicer that is powered by a measure.

Demonstrated below, expected behaviour
- User clicks on monthly, it changes the data in the Matrix Tables to monthly data only

- User clicks on weekly, it changes the data in the Matrix Tables to weekly data only  

I have a table called Metrics, which these three options: Weekly, Monthly, Year.

My switch statement which i wrote is not working, and is as follows: 

MetricsSwitch =
SWITCH(
TRUE(),
"Weekly" IN ALLSELECTED(Metrics[Metrics], FILTER(DATESMTD('CCC - 08042021'[EventDate]),
"Monthly" IN ALLSELECTED(Metrics[Metrics], FILTER(DATESYTD('CCC - 08042021'[EventDate])
)

Is anyone able to help me out here? I'm hoping to build this level of switching in across my dashboard for my users, as they are always wanting to compare (quickly) weekly and monthly data.

[v-yiruan-msft · Accepted solution]

Hi @albertax ,

You can refer the following links to get it.

Change time period (week, Month, Quarter...) on slicer selection

Power BI How To: Adding a Monthly/Annual switch to a Dashboard

Measure to show the data as "daily","weekly","monthly" in power bi?

---

There are 2 common ways to make this work :

Way-1 : Utilizing Power Query

1. Create a "Date Table" using the date column in power query
2. Add 3 calculated columns that extracts month, week and day info from the "Date" column in the newly created "Date Table".
3. Now, unpivot the "month", "day" and "year" columns and load the file.
4. Create a one to many relationship with "Date" columns as common in both tables and there you go.

Way-2 (Using Drill-through buttons).

Use the recent feature update of Power BI, i.e., drilling the data using buttons. So, you just need to create a date hierarchy and then drill through using buttons for "month", "week"and "day".

I hope this will help you.

If you still got the doubt then please send me the dataset and I will give you an detailed explanation on this.

---

Best Regards

[johnt75]

Your best bet is probably a calculation group with items for the different granularities. You can put the items into a slicer and it will do exactly what you're after