Forum Discussion

thedon16's avatar
thedon16
New Member
11 years ago
Solved

Charting Aggregated Monthly Data in Designer

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 t...
  • konstantinos's avatar
    konstantinos
    11 years ago

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

     

mhenning's avatar
mhenning
Advocate I
11 years ago

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.