PowerPivot x-axis date values

LlewHolts

New member
Joined
Jul 13, 2015
Messages
4
Reaction score
0
Points
0
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.
 

Attachments

  • Traffic Conversion_excel.png
    Traffic Conversion_excel.png
    14.8 KB · Views: 21
  • Traffic Conversion_powerbi.png
    Traffic Conversion_powerbi.png
    28.6 KB · Views: 13
Try MediaFire.com or any other file sharing site.
 
File links

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.
 
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
 
PowerView is a BIG toy and will not let you do all the stuff PowerPivot can do.
 
Back
Top