Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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

1 ACCEPTED SOLUTION
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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

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
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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.