I have a data set where below is an example.
I need to find the price with the latest start data, filtered by project no, task code and code.
So for Project no 032839-003 for code "E2" and task code "blank", the correct price is 725 since the latest start data is 01-01-2016.
Hi All, this is my first post.
I am very new to PowerPivot and I want to recreate a calculation that I have in my normal pivot table in power pivot.
Lets say I have 2 columns; Column 1: UserName, Column 2: Date. I want a PivotTable/Chart with Distinct New users by date (who have visited my...
Can I know , how to establish relationship between two tables in PowerPivot.when two foreign keys in same table(Match) referencing primary key in another table( Team).Match ( Match_Id (PK) , Home_Team(FK) , Away_Team(FK) )Team ( Team_ID (PK) , Team_Name )Both Home_Team and Away_Team columns are...
I currently us the tiny script:
Public Sub UpdateAllPVT()
To refresh all my Powerpivot tables in a Workbook. How would I go about writing a script that would just refresh selected tables? Ideally I would like to refresh two separate groups of tables...
I am working on a forecast accuracy report that runs weekly - I have a custom dates table ('SCMWEEK') that contains weeks with WEEK_IK being the Key.
I have made a report using PowerPivot and have connections to both Analysis Services and Excel Spreadsheet (the same spreadsheet that is the file). My colleagues have now starting to use the report and they are refreshing the data and everything is OK expect for one colleague. She...
I have a trouble with getting ModelMeasureNames collection of Power Pivot model by using Excel/VBA. I can't find any function that would allow me to do this. I can get the Power Pivot model of the workbook by ActiveWorkbook.model. But this model object does not have any...
I am new to power pivot (Less than a week). I am trying to calculate determine the cost difference for a given product each year compared to a give n reference year. My data is as below:
<colgroup><col span="3" width="64" style="width: 48pt;"></colgroup><tbody style="border: 0px...
I have two tables in my PowerPivot (see example below). The first one (Table A) shows different packages at different hotels and how much they cost. The second table (Table B) shows how much discount we can get from the different hotels. The discount is based on when the guest are staying...
I've linked a data set in one workbook (multiple tabs with identical columns) to a powerpivot in another workbook using a modified SQL in the table properties 'query editor'. The powerpivot loaded all 12 tabs from the data set without an issue.
However, when I change the data in the data...
Hi, I would like to create a column in the powerpivot data table that will change upon updating a master date or criteria.
I have a functioning If statement that works well: =IF([year]=2010,[count],0)
Which provides the result that I need when I hard code the number 2010 into the if...
Hi Following on from my post about SLAs I think I need to do this via a calculated field so I can use this as KPI field.
So... I think I need to something like this = If(Count([Ticket_CloseMonthYear]) && Count([Ticket_WithinCloseSLA)=1)... then I get run out of ideas... how do I divide the...
I'm trying to measure SLA performance, and as automated as possible so idealy the maths caluclation would be SLA compliant tickets/Total Calls. So in my data model I have a field ticket [ticket_withinclosesla] the date field. I'm just struggling with how to write the expression. Ideally I want...
This is my first time posting, so please excuse any accidental forum faux pas. I am trying to create a pivot table that summarizes how many parts remaining at each process sequence. Ideally, when it's complete it'll also be able to drill down on each sequence to see the number of...
I have a table with sales of individual products, along with their sales order ID. I want to add a calculated column that will indicate if a certain product was contained in the sale. It might be easier to give an example. In this example I want a calculated column that will indicate if the sale...
I have a fairly complex (for me!) Excel 2013 spreadsheet using Power Query and Power Pivot to pull information from various SharePoint Lists, CSV files and OData feeds from other Excel spreadsheets to provide Actual vs Budget on the Maintenance Costs of the houses we have. (We are a Charity...
Firstly, let me preface this enquiry by saying that while I'm proficient using Excel I'm relatively new to PowerPivot/DAX. I would like to set up a persistent model that can automatically calculate the date at which equipment is to be next maintained. Note I have structured this...
I want to display prices using a step chart (Y=Price, X=Date).
The reason I need Step Chart and not a regular line chart is because the prices remain the same till new update arrives.
Anyone did it?
I saw explanations how doing it on the web, but all of them used a predefined range...
I am currently trying to create a PivotTable for my company however, I cannot get the data from my worksheet to transfer over! I am selecting the cells containing the data, however an error message continues to pop up!
Any suggestions would help! Thanks!