A Dating Conundrum in Power Pivot/PowerBI

[masterelaichi]

Hi,

So as the title suggests I have a problem with Dates in Power Pivot. As you might have guessed it involves sorting dates in a chronological order. Just so you know I have looked around online and found solutions on how to sort dates using Month(), Format(text,"mmm") and sorting the month name by month number. But my problem, I think, is beyond that due to my limited knowledge of and experience with PowerPivot/ Power BI

So in a nutshell this is what I am trying to create- a scatter chart for Help desk call stats that shows the volume of calls among other parameters such as abandoned call, average answer speed etc. This, by the way, is just one part of a larger project I am working on

The Dates that I have are in individual time stamps, i.e calls for each day of the month. Hope the sample below


Timestamp Number of Calls Self service calls Chat calls Average Abandonment Rate
29/04/2014 12:00:00 AM 56 2 18
30/04/2014 12:00:00 AM 31 2 3 11
01/05/2014 12:00:00 AM 31 1 3 18
02/05/2014 12:00:00 AM 41 4 0 19
05/05/2014 12:00:00 AM 43 4 0 17
06/05/2014 12:00:00 AM 36 7 0 16
07/05/2014 12:00:00 AM 37 6 0 16
08/05/2014 12:00:00 AM 29 5 0 14
09/05/2014 12:00:00 AM 62 4 0 16
12/05/2014 12:00:00 AM 91 11 4 19
13/05/2014 12:00:00 AM 77 22 2 17


When I try plotting this using a PowerBI scatter chart I get a chart with bubbles trailing all over the screen due to the number of different days. What I want to see is a scatter chart for the total volumes of calls, etc by the month instead of the individual dates

I tried formatting the dates using MonthNumber=MONTH([ServiceDesk Phones]) and Month=FORMAT([ServiceDesk Phones],"MMM-YY") and sorting it by the MonthNumber. Though that displays the play axis on the scatter chart by Months, I still get a messy graph with bubbles and lines all over the screen

Is there a way to work around this? Hope I have been as descriptive as possible

Thanks in advance

 

[masterelaichi]

I just realised that I have mentioned Power BI. It should be Power View and not Power BI. Sorry for the error

 

[Kazlik]

If you want to see the total calls per month you could try something like this where you group by month. I bet there is a simpler way to do this but this worked in an example file I created where a scatter chart displayed as I think you wanted it.

Created calculated columns
Month
=month(Sheet1[Time])

MonthMax
=CALCULATE(COUNT(Sheet1[Month]),FILTER(Sheet1,Sheet1[Month]=EARLIER(Sheet1[Month])))

Create Measures
total call volume:=CALCULATE(MAX(Sheet1[MonthMax]),FILTER(Sheet1,Sheet1[Month]=Sheet1[Month]))
Average Speed:=CALCULATE(AVERAGE(Sheet1[average answer speed]),FILTER(Sheet1,Sheet1[Month]=Sheet1[Month]))

In your chart add Average Speed measure to the X Value, total call volume to the Y Value and Month to the Color.

 

[scottsen]

Generally what you want to do is create a separate Calendar table, then create a relationship from this (fact) table, to the new Calendar (lookup) table. In the Calendar table you are going to have a Month column (Jan, Feb) and a MonthNum column (1,2,...) and tell the Month column to SortBy the MonthNum column.

Then hopefully all is right w/ the world

 

[masterelaichi]

thanks for your responses

I tried creating a new table and called it "Calendar". I get an error when I try to create the MonthNumber Column. However, there was a slight improvement when I was playing around with the dates, I have now somehow managed to have a play axis where the calls are for the entire month. But the only problem is that it is not chronologically ordered. It goes Jan-2015, Feb-2015, April-2014,Mar-2014,May-2014.....Dec-2014

Any suggestions?

Thanks

 

[masterelaichi]

Generally what you want to do is create a separate Calendar table, then create a relationship from this (fact) table, to the new Calendar (lookup) table. In the Calendar table you are going to have a Month column (Jan, Feb) and a MonthNum column (1,2,...) and tell the Month column to SortBy the MonthNum column.

Then hopefully all is right w/ the world

How do I go about fitting the years in the new lookup table?

 

[scottsen]

It will help us if you tell us *what* error you got...

Create a new calc column... =YEAR(Calendar[DateKey]) and poof! You have a way to filter on year.

To fix the sort order, typically you do sometihng like =[Year]*100+[MonthNum] to get a sortable numeric value for the Year+Mo column...then click the "Sort By Column" button in Power Pivot to tell the text version to "sort by" this numeric version.

 

[masterelaichi]

It will help us if you tell us *what* error you got...

Create a new calc column... =YEAR(Calendar[DateKey]) and poof! You have a way to filter on year.

To fix the sort order, typically you do sometihng like =[Year]*100+[MonthNum] to get a sortable numeric value for the Year+Mo column...then click the "Sort By Column" button in Power Pivot to tell the text version to "sort by" this numeric version.

it is not any specific syntactical error due to a wrong formula. It is more to do with the way the scatter chart is displayed.
I am unable order my timeline on the play axis as Jan-2014, Feb-2014,....Dec-2014, Jan-2015, Feb-2015 etc. Either I get a play axis whose dates are in alphabetical order or it goes Jan-2015, Feb-2015, Jan-2014, Feb-2014 etc

I even tried creating a separate Date Table which was suggested in PowerPivotpro.com and linked the dates from the CallStats table to the Date table. That didn't fix it either

I will try this fix that you suggested tomorrow when I get back to work and see if it works. I will let you know how it goes

Thanks :D

 

[masterelaichi]

To fix the sort order, typically you do sometihng like =[Year]*100+[MonthNum] to get a sortable numeric value for the Year+Mo column...then click the "Sort By Column" button in Power Pivot to tell the text version to "sort by" this numeric version.

wow that worked!! I have no idea what the logic behind it is but it worked. A simple fix for something that I have been struggling with for a long time

Thanks Scott!

 

[masterelaichi]

Hi,

It seems like I have run into a whole new set of issues. I want to extend the calendaring feature onto the other charts that I have in my work book. I created a separate DateTable with a DateId, Month Number, Month Name, Year etc

I was then able to create a link from my other tables to this new DateTable. The problem now is that when I add the Date field into my power view report, and try changing the dates, all I get is a blank report. The options I see once I add my Date field to the filter list are All, Blank (Not sure why this comes up), Jan-2014, Feb-2014....

When I select a a specific set of dates, ones where I am sure that there is some data, the report returns a blank. Only time I get some chart is when I select Blank option. I have no clue why this happens

Any ideas?

Thanks