Power Pivot :: Using StartDate And EndDate From Timeline In Calculated Measures?

Jul 31, 2015

For some of my calculations it is mandatory to get the first and the last date, filtered by the slicer type "timeline".

This works well as long as the date is not used on rows or columns ...

As soon as I'm using theDate within the rows this very simplistic dax measure doesn't work ...

I'm wondering how to determine the StartDate (from the timeline) and the EndDate (from the timeline) when "thedate" is actively contributing to the filtercontext.

In my opinion it is not another example of the "event in progress" situation, due to the fact that start and end dates are actively selected by the user and can not be derived from within the date model.


View 2 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 :: How To Convert Measures As Text To Actual Measures

Jul 5, 2015

I have an old model that unfortunately had to be re-establish. 

In order to save time, I thought that I can export all my measures and paste it as measures in my new model. 

I used the following technique to export the measures from the old file: [URL] ....

How to use the output and create the identical measures in my new model, without the need to manually write each one of them?

View 7 Replies View Related

Power Pivot :: Slicers And Timeline Not Working Together

Sep 28, 2015

I have a boolean calculated column on my date table called 'Is Weekend?' that, unfortunately, doesn't work at all when sliced in combination with a Timeline slicer that's applied to the same date table.

For example: selecting an item from my 'Is Weekend?' slicer will reset any filters on the Timeline slicer.  Similarly, selecting an item from my Timeline slicer will reset any filters on my 'Is Weekend?' slicer.

This appears to be the case for all filters applied to the date table, i.e. the Timeline slicer will reset them and they will reset the Timeline slicer.

View 3 Replies View Related

Power Pivot :: Measures Not Reflected In Pivot Table

Sep 18, 2015

I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem. 

What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?

View 3 Replies View Related

Power Pivot :: Parameterized FYTD And LFYTD Measures

Jul 10, 2015

is there a way to create parameterized FYTD and LFYTD measures and avoid hardcoding fiscal year end date in the DATESYTD function ?

I have tried using both VALUES and LOOKUPVALUE to retrieve fiscal year end date inside DATESYTD, but it apparantly only accepts a literal.I have created a Calendar table with e.g. FiscalYearIndex which is 0 for current fiscal year dates and was thinking somthing with CALCULATE and a filtered Calendar table based on FiscalYearIndex = 0.

View 3 Replies View Related

Power Pivot :: How To Stop The Grid From Re-ordering The Measures

Jul 6, 2015

I have a PowerPivot table with lots of measures.

How do I stop the PowerPivot Grid from re-ordering the measures?

This is a very significant issue from my perspective since currently everytime I open the PowerPivot model the ordering of the display of the design of the measures is changed every time the PowerPivot model grid is opened.


View 5 Replies View Related

Power Pivot :: Pasting Measures In Grid Getting Slower

Jun 23, 2015

I have a complex model with lots of tables and measures. It's got to the point where pasting measures in the measure grid is getting very slow with a few seconds flashing "finishing". The calculation mode is set to manual and I'm not switching between the workbook and the PP window. How to speed this up?

View 2 Replies View Related

Power Pivot :: Define SSAS Tabular Measures In Batch?

Nov 19, 2015

We have an Excel PowerPivot model that we are trying to move to SSAS Tabular. The model is complex and has over 300 measures. To group these in Excel, we pasted in dummy tables and defined the measures in logical groupings. Now when we are migrating to SSAS, we have to delete the pasted tables --that have ALL of the measures, in order for the import to work successfully.

How to migrate the model? We have a nice report of all of the measures from DAX Studio, but I am not looking forward to manually re-entering 300 measures through the user interface. Is there any way to define measures in batch? Or is there a way to change the pasted table to a file so the import will work? Will the import work in SSAS 2016?

View 3 Replies View Related

WeekOF Startdate And Enddate

Jan 3, 2008


If i pass a date(03/01/2008) i need to get the startdate(31/12/2007) and enddate(05/01/2007) of the week. Is there any functionallity in SSRS 2005 to do this.


View 3 Replies View Related

Filtering On Startdate And Enddate Question

Aug 1, 2005

Say I have a table which has the validity of an offer as two seperate field, being startDate and endDate:


ID startDate endDate
1 2005-09-01 2005-10-01
2 2005-06-20 2005-10-20
3 2005-07-18 2005-08-16

Now say I want to filter on this data by startDate and endDate, however I have to return any offer that is valid for a specific period.
E.g. if I filter by startDate = 2005-09-10 and endDate = 2005-10-01 then I want it to return the offers with the ID of 1 and 2 as both of these are valid for the dates I'm filtering on.

I can't for the life of me figure out how to do this at the moment, maybe it's too early in the morning to be thinking about this.

Any help is greatly appreciated.


View 6 Replies View Related

StartDate And EndDate Should Be Greater Than Current Date

Jun 27, 2014

I want to know how to make StartDate and EndDate should be greater than current date....

View 4 Replies View Related

SQL Server 2012 :: Calculate EndDate From Subsequent StartDate

Jul 28, 2014

Here is sample data I am working with:

Create table cattimelines (categoryID int, EffectiveDate datetime, CategoryValue varchar(11))

INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-01-01', 'A')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-02-01', 'B')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-04-01', 'C')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-07-01', 'A')

I need to calculates a term date for each record which will be 1 day before the effective date of any new record, thus:



View 3 Replies View Related

Select Repeat Events Based On Startdate And Enddate

Nov 4, 2014

I have one table,

EventId EventName EventStartDate EventEndDate RepeatCount StartTimeEndTime
1Event1 2014-11-04 00:00:00.0002014-11-25 00:00:00.000 4 4:30 PM6:30 PM
2Event2 2014-11-05 00:00:00.0002014-11-12 00:00:00.000 2 3.00 AM4.00 AM
3Event3 2014-11-01 00:00:00.0002014-11-15 00:00:00.000 3 1.00 PM 2.00 PM
4Event4 2014-10-30 00:00:00.0002014-11-06 00:00:00.000 2 5.30 PM 6.30 PM
5Event5 2014-10-16 00:00:00.0002014-11-16 00:00:00.000 0 5.30 PM 6.30 PM

I want to display 2 weeks Events from Table 1 depends on repeat count

(if event scheduled on Tuesday(2014-11-04) and repeat count is 4 then it should repeat the 2 row of data)

We have to display only upcomming events, if the repeat count is 0 and the startdate and enddate falls by todays date then it should display as a single row.Data's Should be ordered by recent date

Expected Result

S.NoEventIdEventName EventDateTime
11Event1 2014-11-04 00:00:00.000
22Event2 2014-11-05 00:00:00.000
34Event4 2014-11-06 00:00:00.000
43Event3 2014-11-08 00:00:00.000
51Event1 2014-11-11 00:00:00.000
62Event2 2014-11-12 00:00:00.000
73Event3 2014-11-15 00:00:00.000
85Event5 2014-11-16 00:00:00.000

Assume today's date 2014-11-04 00:00:00.000 and time now 3.40 PM

View 8 Replies View Related

Power Pivot :: DAX - Min / Max And Average On Calculated Measure

Jun 5, 2015

I have a calculated measure that is "Cost Per Patient:=sum([ActualCost]) / sum(Patients[PatientCount])" and it works fine. I'd like to be able to create another calculated measure based on "cost per patient" that uses min, max and average of "cost per patient" but dax wont let me.

I've read about dax calculate but not sure how to use it in my situation. Here is background on the problem:

There are 2 fact tables, prescription cost and patient count. Both are related by a practice dimension. The practice dimension has a geography hierarchy of county - town - practice. 

The "Cost Per Patient" works fine with the geography hierarchy. By that I mean it shows the correct value when looking at the different levels. For my new calculation, for example looking at the list of counties, I want to use the min, max and average of "Cost Per Patient" of all counties. To break the problem down, I was going to start with creating and testing separate calculated measures for min, max and average and that's where I am stuck.

My understanding of the problem is it's something to do with the context of where the calculation is being made. As I want to perform the min, max and average on all the children of the parent. This article explains it but I can't figure out how to apply it to my situation [URL] ....

View 5 Replies View Related

Power Pivot :: DAX Calculated Measure For Sum Of Max Values

Sep 21, 2015

We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice.  The example below shows the Max sales per month over the last 6 months. 

Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units).  Brandx should roll up to 50 units.  Can DAX handle this in a single Calc Measure?

Brandx          50
    Prod1        20
    Prod2        30
Brandy          60
    Prod5        40
    Prod6        20

View 2 Replies View Related

Power Pivot :: Calculated Measure Maxing Out RAM

Nov 6, 2015

I have a calculated measure in Power Pivot that takes too long to run that it would max out my RAM when running it in Excel 2016 (for some reason it actually manages to run in Excel 2013 but still takes a long time). How to optimise the measure? I have shared the measure below. it is the Unique Leads (MTD) measure that I need to run in a pivot table that is broken down by everyday in a month. The Unique Leads (MTD) measure basically calculates the month-to-date unique leads where a unique lead is defined as an email address that has not appeared in the 'application' table thus far in a particular month.

Unique Leads (MTD):=[CumulativeUniqueApps]-CALCULATE([CumulativeUniqueApps], filter(allexcept('application', Affiliate[Affiliate], 'Lead Type'[Loan Type]), CALCULATE([CumulativeUniqueApps], filter('application', EARLIER([Date])< [Date]))))

CumulativeUniqueApps:=calculate(DISTINCTCOUNT([Email]), filter(allexcept('application', Affiliate[Affiliate], 'Lead Type'[Loan Type]), CALCULATE(DISTINCTCOUNT([Email]), filter('application', earlier([Date])<=[Date]))))

View 16 Replies View Related

Check User Supplied Date Range Is In Between Startdate And Enddate?

May 3, 2012

I need query which will check user supplied date range is in between the existing table startdate and enddate.

if any of the date of user supplied date range is in between the tables start date and end date,it should retrun that record from table.

for example user supply date range is from 1 may 2012 to 5 may 2012.

then query must check that

1 may 2005
2 may 2005
3 may 2005
4 may 2005
5 may 2005

(all dates) is in between startdate and enddate of existing table.

View 2 Replies View Related

Power Pivot :: Combine Calculated Measure With Filters

Jul 9, 2015

I try to calculate the turnover of an employee based on all sales on this customer and the hours spends working for this customer.To simplify, I have 2 tables.General Ledger table.I have customer no and sales.And activity table,I have customer no, hours and users.To be able to calculate the average of a customer, I made an average value based on the sales/divided by the hours on activity table.Customer 1 sales of 100$.Value of the customer is 10$/H -> John 40$ / Mike 60$.How can I use the information 10$/h to use it with the hours from a employee.

View 3 Replies View Related

Power Pivot :: DAX Calculated Column Formula Using SEARCH

Oct 30, 2015

I have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.

View 2 Replies View Related

Power Pivot :: Calculated Measure Based On Condition?

Sep 3, 2015

I have a requirement in Power Pivot where I need to show  value based on the Dimension Column value.

If value is Selling Price then Amount Value of Selling Price from Table1 should display, if Cost Price then Cost Price Amount Should display, if it is Profit the ((SellingPrice-CostPrice)/SellingPrice) should displayMy Table Structure is



My Report Output should be look like 

If tried the below option:-

1. Calculated Measure:= If(Table[Category]="CostPrice",[CostValue],If(Table1[category]="SellingPrice",[SalesValue],([SalesValue]-[CostValue]/[SalesValue])))
*[Sales Value]:=Calculate(Sum(Table1[Amount]),Table1[Category]="SellingPrice")

Tried this in both Calculated Column and Measure but not giving me required output.

View 4 Replies View Related

Power Pivot :: Lookup Inside Calculated Column

Oct 13, 2015

My simplistic data model looks like this: 


Spread Product Sales Channel ..

2 x CB ..
2 y CB …
4 x GFR ..
4 y GFR ..

I need a column with Spread Mapping, which should look like this:

->There are more columns in data model. 
-> Spread for GFR is wrong so it must be mapped to be able to compare with CB

I guess there should be calculated column formula to calculate Spread Mapping. How to do it?

View 6 Replies View Related

Reporting Services :: Unable To Generate Report With URL With StartDate And EndDate Parameters?

Aug 14, 2015

i have problem generating the SSRS Reports. 

i have Report URL  with 2 parameters-- @StartDate and @EndDate

when i hard coded the values and copy the URL in IE, Report gets generated

sample URL's:


but when i use Parameters--Report is not getting generated.


Below is the error;

Reporting Services Error

The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)

SQL Server Reporting Services

View 2 Replies View Related

SQL 2005 Reportingservices - Validating 2 Date Fields (ex: StartDate Should Not Be Greater Than EndDate)

Jan 15, 2007

I have created a report in SQL reporting using serveral parameters including a date parameters called
StartDate EndDate

Now my requirement is, in ReportViewer I need to validate in such a way where
"StartDate" should not be greater than "EndDate".
So kindly help me out by giving me possible solution for the above issue.

thanks in advance,
Ramesh KS

View 1 Replies View Related

Power Pivot :: Measure Calculated On Leaf Level And Rolled Up

Jun 19, 2015

I have the following scenario. I want to apply some calculations on different levels and then aggregate them up.

First measure calculates at Productgroup,color,store,size level

ProductGroup Color Store Size Amount Quantity

Measure:Amount*Quantity   (ProductGroup, Color, Store, Size)

A Blue Store A L 100 6 600
A Red Store A S 150 4 600
A Green Store A M 160 7 1120
B Blue Store A L 300 3 900

[Code] ........

The other measure ignores color

ProductGroup Store Size Amount Quantity

Measure:Amount*Quantity   (ProductGroup, Store, Size)

A Store A L 100 6 600
A Store A S 150 4 600
A Store A M 160 7 1120
B Store A L 640 15 9600

[Code] ...

Ignoring that gives another figure for productgroup B. In the pivot, I should see both measures at whatever attribute, except for the measure that excludes color will be null if tried split on color

ProductGroup Amount Quantity (ProductGroup, Color, Store,   Size) (ProductGroup, Store, Size)
A 410 17 2320 2320
B 640 15 2820 9600
C 170 5 430 430

How should the two measure be defined:

Measure (ProductGroup,Color,Store,Size)
Measure (ProductGroup,Store,Size)

View 2 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 :: Days Of Supply Put Into Buckets / Why Can't Use A Calculated Field

Sep 29, 2015

What I'm looking to do is very much the image below. I need to bucket the DOS. I cannot do a calculation in a column because the DOS needs to recalculate based on filter changes or how graphs are setup. My DOS is a calculated field. When I try to create a calculated field to create the buckets I can't use it in a pivot table or powerview.

View 3 Replies View Related

Power Pivot :: DAX - Lookup Scalar Text Value With A Filter In Calculated Column

Jun 23, 2015

In a calculated column I am trying to get a scalar text value from a lookup to another table. This works quite well when getting numerical values with the following formula:

=MinX(Filter(LookUpTable;LookupTable[from]<=MySourceTable[Day] && LookupTable[to]>=MySourceTable[Day]);LookUpTable[numericalColumn])

But as soon as I substitute the numerical column by a string column, #error results.

I also want to mention that the above query yields only one row as a result. It should be simple to return the value of one of the columns but after searching for quite some time, I could not find any function for that.

View 5 Replies View Related

Power Pivot :: Running Rank Calculated Column That Obeys Context Filters

Jul 13, 2015

I am looking to add a column to one of my tables that displays a running rank of how many times a customer has ordered in a given period. 

I currently have such a column however this column ranks against ALL of the orders that a customer has placed and ignores filters, whereas I need one that ranks based on the filters that are active at any given time. 

The current formula is:

CustOrderCountPersistant=RANKX(FILTER('Q1 Data Set',[k1_customer_id]=EARLIER([k1_customer_id])),[order_id],[order_id],1,DENSE)

For example, if I am looking at a full years worth of data and a customer has placed 10 orders in that period this formula will add a 1 in the column for first order, a 2 for the second and so forth all the way to 10, the last order. 

However it will give me exactly the same results if I filter the data to just one month of that year where they may have order only 2 orders. 

In this scenario I want to have another column with a table that is filter sensitive and would show 1 for the first order and 2 for the second order. 

Now, I do understand that the issue here is probably the FILTER() I have on as, if I understand correctly, that means all other filters are ignored. My attempts at reworking the formula to remove this have been unsuccessful (such as using a CALCUALTE and trying to use filter properties within that forumula).

To explain the context - I want to create a measure that counts how many customers have placed x amount orders in y number of days e.g. how many customers have placed 2 orders in 30 days.

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

Getdate() >= Startdate And Getdate() <= Enddate

Oct 4, 2000

Please i need an exmple of ur solution, thanks :)

I'm using some files to show certain pages on certain date for an example

File name : aa.doc
start date: 10/02/00
end date : 10/03/00

But it expires on 10/02/00, here is the strored procedure:

Before the date comes, it expires the page
Here is my stored procedure:

SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "

Thankx a lot

View 1 Replies View Related

Problems With Filters/Drillthrough/calculated Measures

May 20, 2008

Wondering if anyone has any work arounds.

We have a cube that has financial data in it. There are report actions on cells. I want them to be able to drill through the the supporting data stored in the fact table and all the measures I am showing are calculated measures.

Users connect to this cube from Excel 2007. If they use a "Filter" in Excel and do not select "All" or "One" of the members of the filter hierarchy, then the action dissapears. Microsoft explained this is because that cell then has a calculated cordinate and they can't determine the action to perform.

Has anyone experienced a need to do this? Can you provide me alternatives?



The functionality I am looking for is not available in the BIDS either (Well it is to a point--if you select the first 2, 3, 4...members in the filter, the action will still appear. It only reads the first one however. If you select the members starting with any but the first, the action disappears).


View 5 Replies View Related

Analysis :: Calculated Translated Measures - Underlying Data

Oct 7, 2015

I am trying to develop a cube with translated calculated measures in it. I have also translated the all underlying measures/dimensions being used in action for a header.

I can see my translated measures and dimension in excel but when I go to Underlying data, I see default member's names not the translated one. Please note that I have translated all underlying members.

View 4 Replies View Related

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