Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
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.
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:
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:
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Check out the November 2023 Power BI update to learn about new features.