Dynamically Change the aggregate on a line chart by days or months
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.
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.
Create a measure Date Field Flag that returns 1 if the current field parameter value corresponds to the date selection.
Ensure there is an explicit "Start of Month" or MonthYear column in the Dates table.
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] )
AND ( NumMonths = 1, CurrentDateField = "Date" ),
AND ( NumMonths > 1, CurrentDateField = "Start of Month" )