Power Pivot :: Date Fields Do Not Allow Value Filtering

Sep 8, 2015

I have an Excel 2010 Pro pivot table that uses a Power Pivot view sourced to a SQL Server 2012 SQL view.  This view contains multiple date fields, and when I attempt to use the 'Value Filter' on one of these, the data is not filtering.  The data type in the SQL view is a date.

After attempting to set a Value Filter, and seeing that the filtering didn't work, when I go back to the Value Filter criteria (in my case, I used 'Greater Than' option), the date value that I plugged in to filter has been replaced with a numeric value.

View 3 Replies


Power Pivot :: Where Is The Calculated Fields / Measures Area

Jun 5, 2015

This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?

View 5 Replies View Related

Power Pivot :: Not Showing Data Because It Is Not Clear How These Fields Are Related

Apr 1, 2015

Purpose - relate Server to Software, and Server to App and display it in power view.Current State - I am able to display that Server/Software and Server/App combos separately in power view.  However when I add Server/Software/App it throws the warning as seen below.  This leads me to believe the relationships aren't transitive so to speak.

Supporting Info - v_gs_installedsoftware is the software table.  It relates to v_r_system which is the server table through resource id.  The software table can have many rows with the same resource id, however the server table only has unique resourceids (think of it as a server can have many software products installed).  Table1 is the application table, it relates to the v_r_system table through the resourceid.  Each server from the server table can have many applications that it actually plays a role in (think of a web server that hosts multiple websites for different business units).

View 3 Replies View Related

Power Pivot :: How To Add Calculated Fields To Every Query In Data Model

Nov 12, 2015

I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever. 

View 3 Replies View Related

Power Pivot :: Show Images In Power View With Power Query On Excel Desktop Version / Office 365

Aug 2, 2015

I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:

import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view

Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files 

View 9 Replies View Related

Power Pivot :: Date Since Value First Appeared (age)

Sep 14, 2015

So, I have the following tables

transactions = [date], errorID, status
calendar = [date], year, month etc .

The critical field here is errorID.

Maybe the best way to illustrate the raw transaction data is by example :

I now need to create a report to show the AGE of errors.

age1 = days since first occurrence
age2 = days since last 'Repeat' status

I tried to ignore AGE for now, and just see if I could get the correct earliest date to show for each errorID.

How to do a MIN on date, ignoring all row context apart from keeping my errorID !!?!

View 2 Replies View Related

Power Pivot :: How To Do Nested Date Calculation With DAX

Aug 9, 2015

I have a CSV file that looks like below

JobOrderId CandidateId From_StatusID From_StatusDate To_StatusID

1234 5000 1 07/01/2015 2
1234 5000 2 08/01/2015

I've read this file in PowerPivot and I need to calculate the time in weeks between the two dates for the same JO, Candidate and when the to_StatusId = From_StatusID. In other words, the number of weeks that took from going From Status "1" to Status "2".

Is it possible to do something like this using DAX? Do I need to create a calculated Column?

View 4 Replies View Related

Power Pivot :: Stock Balance - Last Date

Oct 23, 2015

I am struggling with the Lastdate function. I have got stock balance data and want to show the number of products/models that are on stock at the latest date of the stock balance table.

My DAX formula is as follows:

=CALCULATE(DISTINCTCOUNT('3S-StockData'[Article Model]);LASTDATE('3S-StockData'[Date]))

I get the wanted results for all aggregated product groups, on product/model level however the formula does not give me the information wanted (see screenshot).

Basically, the formula calculates correct, but I want in my example only models shown with the date 2015-10-21.

View 2 Replies View Related

Power Pivot :: Prior Month To Date Calculation With DAX

Feb 9, 2013

I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:

Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))

View 5 Replies View Related

Power Pivot :: Budget Month-to-date Calculation

Aug 19, 2015

I have a fact table with sales data at line item level, meaning lots of rows with sales orders, revenue etc. per product group pr month. Then I have a budget table with the budgetted revenue pr product group pr month, meaning a lot less detailed. I followed the method at daxpatterns.com/budget-patterns/ with the headline "Complete pattern" to make it work. Now I have a working data model where I can see actual and budget as seperate values.

I then need to calculate the actual revenue month to date and the budgetted revenue month to date. For actual it has worked fine by doing:


The problem occurs for me doing the same on the budget figure. Since I don't have a budget per day, but only at "YearMonth" level, I have calculated the amount of workdays per month and then used the DatesMTD formula to get the cumulative workdays during the month. Furthermore I calculated the total amount of workdays in the month and made a ratio between this figure and the cumulative workdays so I get a figure in percent that tells me per day how much of the month that has went by (0-100 %). I would then multiply this percentage with the budget and get the budget month to date. This does not work since my budget figure is not at day level.

How I can get it to work. My desired result will be all the dates during a month in my rows and then actual revenue Month to date in values and a corrosponding figure for the budget.

View 7 Replies View Related

Power Pivot :: How To Convert Date / Time From UTC To Local (CET) Considering DST

Oct 27, 2015

I am importing data from Dynamics CRM online using ODATA in Power Query. All datetimes in the database is stored in UTC and I need to convert these into CET. But it's not as simple as just to add 1 hour to the datetime from the database because due to Daylight. Savings time on half the dates I need to add 2 hours - how can I do this?

View 3 Replies View Related

Power Pivot :: Get Order First Date For A Specific Product And Customer?

Nov 18, 2015

I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.

I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.

Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".

View 2 Replies View Related

Power Pivot :: Count Of Items Between 2 Dates Of The Same Date Column?

Nov 5, 2015

I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015. 

I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.

CountofQ1:=COUNTAX(DATESBETWEEN(DumpLoad1[Start Date],[StrtDate],[EndDate]),DumpLoad1[Start Date])

View 4 Replies View Related

Power Pivot :: Modeling And Reporting With Multiple Date Dimensions

Jun 17, 2015

Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:

- Order created date
- Client due date
- Order first payment date (an order can have multiple payments)
- Order fully paid date
- Date assigned to vendor
- Vendor return date
- Date delivered to client

On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.

- Date vendor recruited
- Date vendor reviewed

At any given point the manager may want a report based on any of these dates. For example;

- Product type by order creation date (fiscal year / month)
- Product type by first payment date  (fiscal year / month)
- Product type by client due date (fiscal year / month)

and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier? 

View 6 Replies View Related

Power Pivot :: First Sales Date By Customer For Specific Product Only

Dec 21, 2012

I have a sales order table that has customer, sales, product and sales date.  I am looking to return the first sales date by customer for a specific product.


Sales Date
First Bike Date


[Code] ....

I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer.  Can I use function to complete?  I have a datedim table as well related to the sales date.

View 3 Replies View Related

Power Pivot :: Total For Specific Date Range As Well As JTD Totals

Jul 8, 2015

In the typical Job Cost Reporting world you can easily create SQL Views / Stored Procedures (or create a report) that can return both Job To Date values along with period values for another date range.  Something like this:

Phase of Work,
Cost Type,
Budgeted Cost,
Budgeted Quantity,
Period Cost,
Period Quantity,
Period Unit Cost,
JTD Cost,
JTD Quanity,
JTD Unit Cost,
Variance between JTD and Period Cost,
Variance between JTD and Period Quantity,
Variance between JTD and Period Unit Cost

But in the tabular BI world I am not certain how to have these values show up in a pivot table or BI visualization.  My current Fact table is essentially every job cost transaction so it is at the most granular level holding Company, Job, Phase of Work, UM, Cost Type, Date, Budget Cost, Budget Quantity, Actual Cost, Actual Quantity.  The Budget values only have a value if the entry was updating the budget and the Actual values hold actual cost for each transaction. If you don't restrict by date you have your Budgeted Quantity / Cost and Job To date Cost / Quantity.

I can generate a Pivot table or chart and not restrict by date and I can get all the Budget Totals and the Job To Date Totals when I have the Company, Job, Phase Of Work, Cost Type.

But what if I want to look at a range like last quarter or yesterday or last week.  I can indeed filter that data in Pivot Table and get the period values, but now I don't know how to have the Job To Date values show up for comparison purposes.

I am guessing maybe a DAX formula (if using Excel Powerpivot or SQL Tabular Model), but I am at a loss.

So in the BI world how do you get Job To Date totals while also viewing the data for a period of time?

Here are some example graphs we would want to show for a single Job.  Let's say we want to show the 10 worst performing cost distributions. 

I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Cost, Period Cost, Job To Date Cost.I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Unit / Cost, Period Unit / Cost, Job To Date Unit / Cost.I would want a graph to show the cost distribution and the variance between JTD and Budget and Period and Budget.

As you can see it all stems around seeing JTD and Budget values vs Period Values. Here is a visualization that may work. I am using PivotTable filtered on one Company, Job, Phase of Work. I can see the sum of actual cost which is the totals for the date range I provided. But I still want to see the JTD totals for that Phase of Work and Cost Types and actually the Budget Totals as that is sort of a JTD, but for budget... especially if I want to compare Budget vs JTD vs Actual.

View 8 Replies View Related

Power Pivot :: Calculating Monthly Totals From Date Ranges?

Jul 28, 2015

I am looking to calculate the monthly holidays by staff member using DAX. I am able to calculate it  if all entries (Start Date & End Date)  are confined to a unique month. The issue is where a person's holidays go across several months

I have 2 tables

Table 1 - Holidays
Name                        StartDate                                                 EndDate
Joe Bloggs                 27July 2015                                             7th Aug 2015
Jenny Jones               22nd July 2015                                        23rd July 2015
Jenny Jones               27th  July 2015                                        28th July 2015

Table 2 -Months&Years
June 2015
July 2015
August 2015
September 2015

I am looking for two types of Output  (high level and detailed)

Month/Year                        Name                         Total  Days OOO
July 2015                          Joe Bloggs                            5
July 2015                          Jenny Jones                          4
August 2015                      Joe Bloggs                          5

High level Month/Year                       Days OOO
June 2015                                0
July 2015                                 9
August 2015                             5

View 4 Replies View Related

Power Pivot :: Date Dimension - Create A List Of Months

Jul 21, 2015

I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as

Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),

The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g


I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.

View 2 Replies View Related

Power Pivot :: Calculating Count Prior To Header Date

Nov 13, 2015

I have two tables - one with dates at the end of each month (10/31, 11/30, 12/31, et al) that's linked to a data table containing client signups. While I can get the count per month in a pivot table, I'm trying to calculate the beginning count of each month.

How do I use calculate or something similar to sum the count of records created minus count of records closed prior to the beginning of the period?

The pivot looks like this right now and works by month:

beginning active records ?????
count of records created 100
count of records deleted 50
net records created 50
running balance in net records created 100 (theoretically the next month's beginning balance, but can't figure out how to reference or calculate)

View 3 Replies View Related

Power Pivot :: Divide Number Of Records By End Date Of Month Using DAX Query

Aug 4, 2015

We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January) 

When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)

writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.

View 6 Replies View Related

Power Pivot :: Get Last Available Value For Measure In Current Context (week / Month Level And Not Date)

Jul 21, 2015

I can't seem to upload images so here's the link: [URL] ....

I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key. 

View 8 Replies View Related

Power Pivot :: DAX Function ALLSELECTED Doesn't Refresh When Using A Date Hierarchy For Rows

Apr 3, 2015

I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer.  The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.

This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc. 

View 3 Replies View Related

Power Pivot :: Calculate Sum Of Actual Sales Until Date And Forecast Sales After A Date?

Sep 30, 2015

I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).

I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.


Unfortunately the measure above does not work.

In addition to the parameter table, I also have a normal date table.

View 2 Replies View Related

Power Pivot :: Creating A Summary Report Using Relationship Between Date Table And Two Fact Tables

Oct 19, 2015

I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.

Month   Count

View 3 Replies View Related

Power Pivot :: Difference In Cost Between Most Recent Date And Second Most Recent Date

Apr 15, 2015

Have a table that list item#, date the standard cost went into effect and the standard cost.  How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.

Item#      EffectiveDate    StdCost

1152        01/01/2009      5.50
1152        09/29/2014      5.81
1152        04/04/2011      5.56


View 2 Replies View Related

Power Pivot :: Create Measure Within Date Dimension Table To Sum Single Entry Per Month Eliminating Duplicates

Jul 27, 2015

We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.

I have a date dimension table (dimdate).  Has a row for every day of every year (2006-2015)

I have a work entry fact table (timedetail).  Has a row for every work entry.  Each row has a worked date, and this column has a relationship to dimdate.

Our holidays fluctuate, and we offer floating holidays that our staff get to pick.  So we cannot hard code which individual dates in dimdate as holidays.  So what we have done is added a column to our dimdate table called HolidayHoursPerMonth. 

This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates.  Below is a brief example of dimdate.  In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.

DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8

I have a pivot table create based of the fact table.  I then have various date slicers from the dimension table (i.e. year, month).  If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.  

I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month.  For example July should just be 8, not  8 x #of days in the month. 

Listed below is how many hours per month.  So if you were to slice on an entire year, the measure should equal 64.  If you sliced on Jan, Feb and March, the measure should equal 12.  If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).

MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8

View 3 Replies View Related

Power Pivot :: Create Power View Report - Button Does Not Show Up For Some Reports

Nov 3, 2015

I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.

View 5 Replies View Related

Power Pivot :: Power Query Error Expression / Cannot Convert The Value To Type Text

Jul 20, 2015

I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example

Expression.Error: We cannot convert the value 40 to type Text.

The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.

View 2 Replies View Related

Power Pivot :: IF In Power Query Statement - Converting Text To Numbers

Nov 18, 2015

Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:

#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))

This works fine if you chose a single location.  However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value.   I was therefore surprised when the same query took 2.5 times longer to refresh????

My PQ now looks like this

  #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))

I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?

View 9 Replies View Related

Power Point :: How To Generate Rank Based On Some Group In Power Pivot

Apr 10, 2015

I have data like below

Country     State            Rank
India         Kerala            1
India         Kerala            2
India         Kerala            3
India         Tamil Nadu     1
India         Tamil Nadu     2
India         Orissa            1
India         Orissa            2
US            Florida            1
US            Florida             2
US            NewYork         1

I have to generate rank like this in power pivot. How can I achieve it?

View 3 Replies View Related

Power Pivot :: Error When Updating From Power Query Source

Jun 11, 2015

When updating a Power Query Source in Power Pivot, I'm getting the following error message:

Basically saying that OLE DB or ODBC-error occured when:

- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel

None of which is the case here. What can cause this?

System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.

View 5 Replies View Related

Power Pivot :: How To Set Refresh For Excel File Uploaded In Power Bi

Sep 9, 2015

I have opened an account in [URL] and taken the 60 days trial for power bi pro. I've developed power pivot and generated power views in share point 2013. But, I'm new to Power BI desktop. I have created a report in power BI desktop and published to [URL]. Also, I have uploaded an excel file directly to [URL] and created the report from the workspace available there itself and pinned the report to dashboard also. Everything is fine till this. But, I need to refresh the file which I have uploaded. I have some dummy data in excel sheet.

ZipCode State ZipName

2345 AA AA
456 BB BB
6787 CC CC

 This has been created as a table and then added to data model. So, power pivot has been created for the same. Then I have uploaded this file to [URL] site. But, I'm getting an error message while trying to schedule refresh for the same.

"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."

How can I create a data model connection? How can I schedule refresh for an excel file like this?

View 3 Replies View Related

Power Pivot :: Power View - Mix Series Types On Same Chart

Jul 25, 2015

I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....

View 3 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved