Forum Discussion

Diabetic's avatar
Diabetic
New Member
1 year ago
Solved

Chart Granularity

I have a graph that shows the Offered, Answered & Abandoned calls from my data set. I have this on the y-axis of a bar chart and then I have created a parameter for the dates on the x-axis. This allows me to use a slicer to swap between the granularity levels (Daily, Weekly, Monthly).To achieve this, I have a date table that has all the dates for this year, along with a column defining the month and a column defining the week commencing date.

 

I want the Daily view to show the last 30 days, the weekly view to show the last 8 weeks and the monthly view to show the last 12 months. This is where I am running into an issue. I can't get the dates to change with the granularity which causes my data to show an entire year of data on all the views.

 

How can I change this so the date range of the displayed data aligns with the desired granularity that is being set by the slicer?

"Date"
"DAX"
"Granularity"
"Need Help"
"Slicer"
  • johnt75's avatar
    johnt75
    1 year ago

    You can try creating a measure which you would use in the filter pane, set to only show when the value is 1.

    Filter Measure =
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR LastDateToShow =
    TODAY ()
VAR __SelectedGranularity =
    SELECTCOLUMNS (
        SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),
        Parameter[Parameter]
    )
VAR FirstDateToShow =
    IF (
        COUNTROWS ( __SelectedGranularity ) = 1,
        SWITCH (
            __SelectedValue,
            "Daily", LastDateToShow - 30,
            "Monthly", EOMONTH ( LastDateToShow, -13 ) + 1,
            "Weekly",
                LOOKUPVALUE ( 'Date'[Week commencing], 'Date'[Date], LastDateToShow - 7 * 8 )
        )
    )
VAR Result =
    IF (
        FirstVisibleDate >= FirstDateToShow
            && LastVisibleDate <= LastDateToShow,
        1
    )
RETURN
    Result

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You can try creating a measure which you would use in the filter pane, set to only show when the value is 1.

    Filter Measure =
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR LastDateToShow =
    TODAY ()
VAR __SelectedGranularity =
    SELECTCOLUMNS (
        SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),
        Parameter[Parameter]
    )
VAR FirstDateToShow =
    IF (
        COUNTROWS ( __SelectedGranularity ) = 1,
        SWITCH (
            __SelectedValue,
            "Daily", LastDateToShow - 30,
            "Monthly", EOMONTH ( LastDateToShow, -13 ) + 1,
            "Weekly",
                LOOKUPVALUE ( 'Date'[Week commencing], 'Date'[Date], LastDateToShow - 7 * 8 )
        )
    )
VAR Result =
    IF (
        FirstVisibleDate >= FirstDateToShow
            && LastVisibleDate <= LastDateToShow,
        1
    )
RETURN
    Result
    • Diabetic's avatar
      Diabetic
      New Member
      1 year ago

      Excellent, that worked brilliantly. Thank you