Dynamically Change the aggregate on a line chart by days or months

[Mike282]

Hi all,

So I've got a problem. I have a dataset that contains values which I aggregate and display on a line chart by date. I want to however have the line chart's date axis dynamically change from aggregating in Days to aggregating in months depending on how I select the date slicer. So for example:

In this example my date slicer is filtering between the 1st of June to the 13th of June which means all data falls into the 1 month of June. I want the chart to aggregate and display values by day./date.

When I then change the date slicer to include more than 1 month for example the below. I want the line chart to dynamically change so that it's now displaying and aggregating by months.

I now want to aggregate it by months instead of by date/days

Is there a way of doing this?

Here's the dropbox link to the PBIX file I used to generate the screenshot. Any help would be greatly appreciated.

https://www.dropbox.com/s/kdrayilm4f7pp6u/Help%20Example.pbix?dl=0

[OwenAuger]

I was just look at this too and saw @Ritaf1983 's post just before I posted.

My only addition to the discussion is that the field parameter selection can be automated.

1. Create a measure Date Field Flag that returns 1 if the current field parameter value corresponds to the date selection.
2. Ensure there is an explicit "Start of Month" or MonthYear column in the Dates table.
3. Add a Top N filter to the line chart visual as shown below.

See attached PBIX.

Date Field Flag measure:

Date Field Flag = 
VAR NumMonths =
    DISTINCTCOUNT ( Dates[Start of Month] )
VAR CurrentDateField =
    MIN ( 'Date Field'[Date Field] )
RETURN
    INT (
        OR (
            AND ( NumMonths = 1, CurrentDateField = "Date" ),
            AND ( NumMonths > 1, CurrentDateField = "Start of Month" )
        )
    )

Visual-level filter on line chart:

[Ritaf1983 · Accepted solution]

Hi @Mike282 
I

While I don't believe it is possible to do it completely automatically, I do believe it is possible to use
Field parametrs
In order to select the granularity level desired

Please follow these steps to modify this option:

1. Add to your date table column for month - year 

2. Add another column to have the option to sort your month and year in the correct order:

3. Modify sort order of month and year:

4. Add field parameters:

5. Put the field parameter as a X axis to your chart:

Now you can change desired granularity from the slicer of the parameter:

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly