PDA

View Full Version : PowerPivot x-axis date values



LlewHolts
2015-07-13, 03:50 PM
Good day,


So I’m struggling with a date issue for some graphs that a client wants me to model the data around. The graphs were initially constructed in excel using a variety of different pivot tables and vlookups (See excel file ‘Booking Engine – Funnel – Anonymised’ attached). I need to recreate the graphs on the ‘Dashboard-IV’ in PowerView based on the data from the ‘UPVData’ sheet and host them on PowerBI for clients to access. I have started with the data modelling and reports (see excel file ‘Booking Funnel Data 2′) no 2,4, and 6 on the right of the ‘Dashboard-IV’ page. These I have no problem with as the x-axis contain normal dates and I can just use the ‘Date’ values from the ‘UPVData’ or Date table.


My struggle is how I can create the x-axis date values for reports no 1,3,5,7,8,9,10? The x-axis uses 5 values:


– Current Month (e.g. May-2015)
– Previous Month (e.g. April 2015)
– Last 3 Month Average (e.g. last three month average)
– Last 6 Month Average (e.g. last six month average)
– Same month last year (e.g. May-2014)


What I don’t know is whether there is a way to model the data so that I can use the date values across all calculations, or whether I need to create these x-axis date values for each calculated value, i.e. Desktop, Tablet, Mobile, Start %, Passenger details %, etc.


Your help will be greatly appreciated. :)


Kind Regards,


Llewellyn

Ps: I see my files are too big to download - any way around this.

Herbds7
2015-07-14, 01:03 AM
Try MediaFire.com or any other file sharing site.

LlewHolts
2015-07-14, 06:53 AM
Ok I managed to load the files on Google Drive - please use the following "https://drive.google.com/folderview?id=0B29yA0i2Te9yRUlBOU5uWWpMdnc&usp=sharing"

Let me know if you can't access the files.

Have a great day!!

Llewellyn

Let me know if you can't access the files.

Herbds7
2015-07-17, 03:01 AM
Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
Converted a small portion of the first file to PowerPivot.
I like the original format and recommend not changing a thing.
http://www.mediafire.com/view/mpki5gte38v45ix/07_14_15d.xlsx

(http://www.mediafire.com/view/mpki5gte38v45ix/07_14_15d.xlsx)

LlewHolts
2015-07-17, 10:55 AM
Good day Herbds7,

This is great and step in the right direction. However, when I try and use these calculated fields in PowerView it doesn't seem to work. It won't allow me to add these calculated fields as x-axis values. Doing these graphs in excel and PowerPivot is not the problem, doing the same thing in PowerView is the BIG issue for me here. I don't know if maybe PowerQuery can help restructure the data in a way that I can do this.

I really appreciate your help!

Have a great day!

Kind Regards,
Llewellyn

Herbds7
2015-07-17, 09:56 PM
PowerView is a BIG toy and will not let you do all the stuff PowerPivot can do.

LlewHolts
2015-07-18, 08:30 AM
So there is no way to do this at all?