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.
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?
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.
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'?
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.
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.
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?
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?
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.
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.
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
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] ....
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?
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.
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.
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.
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
Hi, 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.
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
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.
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.
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:
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.
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.
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
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 "
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?
Thanks
[Edit]
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).
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.
