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

 

[scottsen]

It sounds like the 2 columns you created the relationship on between calendar and fact tables... do not actually match.

 

[masterelaichi]

When I created the date table in excel, and added it to PowerPivot, the DateID format is displayed as
01/01/2014 12:00:00 AM

02/01/2014 12:00:00 AM

<tbody>
</tbody>

03/01/2014 12:00:00 AM

<tbody>
</tbody>

...etc

The other tables in my work book have more precise date fields such as 15/10/2014 08:10:56, 15/10/2014 13:40:21 etc. Could this be the problem? I tried converting the date fields in both the fact tables and the DateTables to dd/mm/yy but it still didn't help



[TABLE="width: 130"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 130"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 130"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

That *is* the problem. no doubt about it. And changing the "format" in power pivot won't help. Best is to strip off the time at the data source. If you can't, then created a new calculated column that has JUST the date (no time), and use that new calc column for your relationship.

 

[masterelaichi]

Thanks. That seems to work. I didn't know that converting the date format on power pivot won't help. I did try converting the date in the source excel sheet. But that didn't work either. I am not sure why

Like you suggested, I created a new column and used the function DATEVALUE(dates) which resulted in a column with the date and a timestamp matching the one in the DATETABLE, i.e, 02/01/2014 12:00:00 AM etc. Is there a formula to strip off the time stamps completely from the date fields? TRIM and TRUNC just gives me an error :|

Thanks

 

[scottsen]

Pure "date" colums still seem to show the 12:00am time... you can safely ignore that. In fact, NOW if you want to change the format to just show the date (and no time) that is fine