Charting Aggregated Monthly Data in Designer

[thedon16]

I have transactional data with sales dates and sales amounts that I want to plot in a line chart with sum of sales for each month.

This seems surprisingly difficult to get charted and sorted in the right order in an intuitive way.  

I've tried variations of month/year formats and the time sorting order in the chart is always wrong.

What is the best practice to do this presently in BI Designer?

[gsalas]

Hi! I am having the same issue.  I have 4 columns from four different tables I wish to display in a combo chart.  The data is daily but combined into weekly data points using links between the 4 different data tables and a single central date table with week ending dates in a secondary column.  When I try to plot the four data points (2 bars, 2 lines) the lines come out completely scrambled.  This is despite the fact that all data sources are sorted in ascending order.  I have to convert the week ending dates to monthly, which I do not want to do.

[mhenning]

If you are keeping the data in azure, you can create a view, that will break out your date information. 

I created a view for sales data that includes the initial date, but added columns for month, date, year, quarter in the view. 

for example...

select

orderdate,

datepart(q,orderdate) as [Quarter],

datepart(mm,orderdate) as [monthNumber],
datename(MONTH,orderdate) as [monthName],
datepart(yyyy,orderdate) as [year],

will send this to power BI

2011-01-30  ,1, 1 ,January ,2011

so I can use those columns in the report to group data a bit more elegently. One thing I dont like, is that if I use the datename in the axis, powerbi will sort alpha. So from left to right my chart starts with April, then August, Etc. I find I have to use the numbers only to get the chart to graph properly from January to December.  I am sure you could do a similar calculation in excel and get similar results. Just use a calculated value for each slice of data and see how that works out.  

[thedon16]

Best solution I've found is to create a column EOMONTH([Dates],0) and use that on the axis.

But for some reason the line chart shows everything one month ahead so 5/31/2015 shows as June, etc.

So I use EOMONTH([Dates],0) - 31

That seems to work.  Very hackish, but it works.

Please tell me there is a better way?

[konstantinos · Accepted solution]

I am not sure if I understand exact your issue..Because you use actual dates & are too many the axis is continuous ( in PowerView you can choose between continuous or categorical which shows the exact values)..If sorting is your problem I usually create 2 calculated columns in the date dimention table 1.  year/ month " 2015 Jul " =YEAR(Date[date]) & LEFT(Date[MonthName)

2. a column to sort like "201507 " = (Year(date[date])*100 ) + MONTH(Date[Date])

Then choose the year/month calculated column & from the ribbon sort it by the sort column.

If your problem is the Visuals (not enough space to display ) follow  @andre suggestions..

[andre]

I often see users struggling when they try to build all visuals based on one very wide table with a large number of columns in it. 

One thing you might consider is breaking your data up in several tables, with one of them dedicated specifically for Date/Time.. In this scenario, you can add extra columns to that date table and also specify sorting order and aggregation attributes such as month/quarter/half year, year, fiscal year, etc.

This tutorial may be helpful in getting you started on how to organize your data so that it is easier to grow your model in the future and to have fewer issues with calcs and visuals.

http://businessintelligist.com/2014/11/21/tutorial-how-to-create-a-star-schema-model-in-power-bi-and...