Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic axis change based on custom date slicer selection

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?

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous, 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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