Forum Discussion

joyhackett's avatar
joyhackett
Icon for Helper II rankHelper II
5 years ago
Solved

Automatic drill level depending on date filter

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 ...
Need Help
  • joyhackett's avatar
    joyhackett
    5 years ago

    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!!
joyhackett's avatar
joyhackett
Icon for Helper II rankHelper II
5 years ago

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!!