Dynamic axis change based on custom date slicer selection

[Anonymous]

Hi guys, I'm looking to create a chart where the Axis changes based on a selection from a custom date slicer. To create the custom date slicer I have used this article http://analyticsavenue.com/power-bi-timeperiod-slicer-for-last-7-dayslast-30-days/

The slicer gives you the options to choose from last week, last fourteen days, last quarter, etc. Once a user clicks on let's say last quarter, I want to be able to change the axis from showing all 90 days on the chart to just three months. The days range works ok for up to 2 weeks, then it starts getting a bit too messy. Has anyone come across something like this?

[v-shex-msft]

Hi @Anonymous,

These formula only calculate fixed date range, I don't think you can direct use last 90 days as last three month, it can't mapping the correct data range.

For your scenario, you can try to use below formula, I use date function to calculate previous date to replace static number of days:

DatePeriod =
VAR TD =
    TODAY ()
RETURN
    UNION (
        ADDCOLUMNS (
            SUMMARIZE (
                CALCULATETABLE ( 'Date', DATESBETWEEN ( 'Date'[Date], TD - 07 + 1, TD ) ),
                'Date'[Date]
            ),
            "Period", "Last 07 Days"
        ),
        ADDCOLUMNS (
            SUMMARIZE (
                CALCULATETABLE ( 'Date', DATESBETWEEN ( 'Date'[Date], TD - 14 + 1, TD ) ),
                'Date'[Date]
            ),
            "Period", "Last 14 Days"
        ),
        ADDCOLUMNS (
            SUMMARIZE (
                CALCULATETABLE (
                    'Date',
                    DATESBETWEEN (
                        'Date'[Date],
                        DATE ( YEAR ( TD ), MONTH ( TD ) - 1, DAY ( TD ) ),
                        TD
                    )
                ),
                'Date'[Date]
            ),
            "Period", "Last Month"
        ),
        ADDCOLUMNS (
            SUMMARIZE (
                CALCULATETABLE (
                    'Date',
                    DATESBETWEEN (
                        'Date'[Date],
                        DATE ( YEAR ( TD ), MONTH ( TD ) - 3, DAY ( TD ) ),
                        TD
                    )
                ),
                'Date'[Date]
            ),
            "Period", "Last three month"
        ),
        ADDCOLUMNS (
            SUMMARIZE ( CALCULATETABLE ( 'Date' ), 'Date'[Date] ),
            "Period", "Overall"
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft, thanks for the solution, however I don't think it provides what I am after. On the chart below you can see that the last three months are still shown as days rather than months. I would like to see three charts for july, august & sept rather than all the days. Any ideas if that is doable?

[v-shex-msft]

Hi @Anonymous,

You can modify your date column to hierarchy mode, then remove quarter and day fields and drill to bottom level. After these steps, your graph should summary by year month level.

If above not help, please share some sample data for test.

Regards,
Xiaoxin Sheng