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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ps
Regular Visitor

Group or Sum up daily data into month so that chart won't be overcrowded with daily data

Hi all,

 

We are trying to display time series data in line chart based on the dropdown slicer. E.g. if I choose Last 7 Days, the line chart will display two lines, one is the last 7 days, another line stacked will be showing previous 7 days (i.e. 7 days before the last 7 days).

 

Our data is continuous daily date (e.g. 01/10/2021, 02/10/2021, 03/10/2021, etc). So for a Last 7 Days data, it looks good because it's showing only seven data points, as for Last 14 Days it is still bearable. However, for Last 30 Days, Last 3 Months, Last 6 Months, Last 12 Months and All (consist of 3 years of data), the two lines in the line chart are "overcrowded" fitting in one small chart and bring no meaning for comparisons.

 

Is there a way to group, auto group or sum up, e.g. for Last 3 Months, just summarise/sum up each month's total and make the line chart to show just three data points because it's "3 months" (e.g. Aug, Sep, Oct) instead of like the screenshot below (which shows all of the daily data points). Same goes to if it's "All" being selected, then only sum up to three data points (e.g. year 2019, 2020, 2021).

 

Screenshot 2021-11-05 222836.png

Here's the measures if helps.

 

x-axis:

PeriodFiltering = 'dashboard'[Period].[Date]

 

y-axis:

TotalCountPrev3Month = CALCULATE([TotalCountWithoutZero],REMOVEFILTERS('SpecialDates'[Period]),(DATEADD('dashboard'[PeriodFiltering], -3, MONTH)))


TestingLinePrevGraphSelection = SWITCH (
    TRUE (),
     SELECTEDVALUE('SpecialDates'[Period])=="Last 3 Months", 'dashboard'[TotalCountPrev3Month]
) 


TotalCountWithoutZero = DISTINCTCOUNT('dashboard'[user_id]) 

 

We are very new to Power BI and have searched for many days but to no avail, could be due to incorrect terminology search. Appreciate any help from the community experts. Thank you!

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @ps ,

 

You can group like as follows.

Put the date hierarchy ‘Year-Month’ into the axis, then create a measure, put it in a filter, and set Show items when the value is 1.

2.png

 

 

Best Regards,

Stephen Tao

 

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

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @ps ,

 

You can group like as follows.

Put the date hierarchy ‘Year-Month’ into the axis, then create a measure, put it in a filter, and set Show items when the value is 1.

2.png

 

 

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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