cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Mike282
Helper III
Helper III

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

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.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/daysI 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

2 REPLIES 2
OwenAuger
Super User
Super User

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:

OwenAuger_0-1686634918217.png

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Ritaf1983
Super User
Super User

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 

Ritaf1983_0-1686633743463.png

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

Ritaf1983_1-1686633822360.png

3. Modify sort order of month and year:

Ritaf1983_2-1686633894715.png

4. Add field parameters:

Ritaf1983_3-1686633947565.pngRitaf1983_4-1686633980687.png

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

Ritaf1983_5-1686634089693.png

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

Ritaf1983_6-1686634142395.png

 

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

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors