Data by day viewed by Month

[brianvpowerbi]

Hello,

My data set is very simple, it has 2 columns, one for Date and the other for Value.  The dates range from January 2011 through current day(by day).  I need to display a line chart where the legend is years, thus showing 7 lines(one for each year) and viewed by month.  However, i do not want to aggregate by month but rather show the unique daily values in the month view.  Please help.

Thanks

[Anonymous · Accepted solution]

Hi @brianvpowerbi,

You can refer to below steps:

1. Switch the Date column to hierarchy mode.
2. Drag Year part to Legend field, Month and Day to Axis field.
3. Drag value to Values field.

Sample:

 

Regards,

Xiaoxin Sheng

[brianvpowerbi]

This does not produce the expected result.  The steps below will aggregate the data by month(since the data is listed daily on the query).  I need individual days presented in a monthly view without aggregation.  There is no option to not summarize the data.

[Anonymous]

Hi @brianvpowerbi,

>>I need individual days presented in a monthly view without aggregation.  There is no option to not summarize the data.

You can add a calculated column to store the month and day, then use this new column as the axis.

Formula:

MonthDay = FORMAT([Date],"MM-dd")

Regards,

Xiaoxin Sheng

[brianvpowerbi]

I think we are getting closer, but now I have two follow up questions:

1. The chart is scrolling, I want to show it all in one picture, so how do I modify the X axis to show incremental ranges(i.e. only show every 20 days or so to fit in one window.
2. with the calculated column as the X axis, it is now sorting alphabetically, instead of by date.  See below.  How do I sort by date.

[Anonymous]

Hi @brianvpowerbi,

I think you can format the display axis to numeric, it may easier to sort.

MonthDay=FORMAT([Date],"mmdd")

Regards,

Xiaoxin Sheng