Automatic drill level depending on date filter

[joyhackett]

Hi,

I have a simple line graph for sales by date (using the full_date hierarchy with Year, Month, Day) and a date slicer.

I have the report saved to default one level level down so the graph loads showing sales over year/month.

If a user selects the last 2 months, it looks silly....

Ideally, I would like the graph to automatically expand one more level (year/month/day) if the user selects a date range that is less than 90 days.

Can this be accomplished in Power BI?

Thanks! 🙂 Joy

[joyhackett · Accepted solution]

I think I may have figured out a way to switch the X axis to show Month if > 90 days in the range, or Week if <= 90 days in the range).

I have 2 date hierarchy columns in dim_date: full_date and calendar_week_start_date.

I have a measure "Day Count" to count the dim_date rows.

I have a calc column that returns "Week" if Day Count <= 90, otherwise "Month"

Then I have a "Date Selection" column:

Date Selection = SWITCH(dim_date[Week or Month],"Week", [calendar_week_begin_date].[Date],"Month",[full_date].[Date])

I replaced the Axis value to "Date Selection" and it works!!

[parry2k]

@joyhackett it will not auto drill down based on the selection, the user has to manually drill down to the next level.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡