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
1/1/2000
1/2/2000
1/3/2000
etc..
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.
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.
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).
I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example, my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.
The solution I have come to so far involves creating a table with a single column that contains all the single values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.
A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric. The example is available here.
I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
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.
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
I am having issues where the PowerPivot Field List does not refresh once I add a new table/field then select Refresh All under Data. I have to refresh every single PowerPivot Field List so they are all refreshed. ("PowerPivot data was modified" message). This doesn't happen at first but once the model is built on it tends to happen.I'm on Excel 2010 PowerPivot 2012 SP2 32bit. I have Windows 7 64bit and I'm wondering if the bitness inconsistency is an issue.
I am trying to import the data from SharePoint into my PowerPivot window as a Datafeed. I am able to successfully import the whole data from SharePoint list. But, now i would like to apply a filter (Where Clause) before importing the data.
I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following
Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.
I tried the the DATEADD function instead but got no luck and data came out wrong!
I am using Excel 2013 64bit and use an english Excel version, but with a comma as a decimal seperator and semi-colon as a list seperator.
In Excel everything works fine, but PowerPivot does not properly recognize that I use a semi-colon for formulas.
PowerPivots lets me write formulas with the semi-colon and not the comma, so that is fine.
However, two issues appear:
the yellow smart formula help box that appears when you start typing a formula, thinks I have to use commas, so when I use semi-colons instead, it does not jump to the next parameter.This problem also causes parameters where I have to enter a table or field to not suggest me table and fields when I start typing.Sometimes the formula validation even throws me an exception, that my formula syntax is incorret, ebcause Id id not use a comma. However, commas also do not work. I have to do some weird playing around until it finally accepts my formula.
I hope this buggy behaviour gets fixed, but is there a way I can work around this without changing my formula/list seperator? I also do not want to use a German Excel version, because I am used to the english formulanames.
I am bring two fact tables and a Date Dim into my Power Pivot model. I try to create relationship where but Power Pivot just reverses them. When I create the relationship I get an info icon warning me it will be changed.Why can't I create a one to many relationship with fact table on many side and dimension on one side?Power Pivot will not let me mark my dimCalnder as a date table either. My date dim has surrogate key, year, month and monthlongname columns only...
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 export PowerPivot from Tabular "Analyse in excel" and i have a connection to the temporary tabular DB of my own user. when i close the Tabular Solution my DB disapire and it is not possible to work. i sopose that i have to create to the steady DB.
Q1-2013 Q2-2013 Q3-2013 Q4-2013 2013 Total Distinct Count Member Distinct Count Member Distinct Count Member Distinct Count Member Member Months AMBULANCE - LAND 264 301 355 352 1272 AMBULATORY SURGICAL CENTER 16 38 30 34 118 COMMUNITY MENTAL HEALTH CENTER 6 7 11 13
I have the above data, and want to create a column adding up the Distinct count member of each quarter. The number of distinct count member of each quarter is from a measure. The result I want to create is the column of 2013 Total Member Months as shown above. How to create a dax formula.
I am trying to write a RANKX Function as a calculated field instead of a measure, so I can used rank as a report filter but it is not working.I have customers that I can rank by sum of sales amount and then bucket their ranks within categories such as Top 10, 50, 100. The goal is then to use this Top Customers list as a filter on the Power BI report.So far I have the following as a calculated column.
It all works except that once we get to the current date, the CumulativeCompleted plateaus (as you'd expect), but the dates (x-axis) keep rolling. Now, I want the dates to roll on so I can project out a linear trendline but I want the graphing of the burndown to stop at today's date.
I'm working on a measure which I'd like to use in PowerView, my data model contains 2 things:
1) Employees' scores 2) Stores in which employees work in
I would like to be able to put the employee's score in comparison to the store average (which is the average of all the employee's scores in the store), and then provide every employee with an individual report on their scores against their store's score (by tiling the view in PowerView by Employee). To do this, my (first attempt) at a measure for the store average looks like the following:
Which gives me the score of the employee vs. all other employees across all stores. What I'd like to then achieve is to add an additional filter that filters only the scores for the store that the employee belongs to - which is where I'm stuck.
works perfectly, but doesn't give me the desired output, which is 1 measure that changes the store depending on which employee (and thus store) is being selected.
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?
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.
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.
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.
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))
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.
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?
I have a table ComponentPeriod. In it we have the combination of a component (e.g. A,B,C ) and a period (2014 Q1, 2014 Q2, 2014 January etc)I want the periods to be in descending order (2015 Q4, 2015 Dec, 2015 Nov, 2015 Oct, 2015 Q3 ... etc) and so I need to create a sequential number series to allow this to happen (as we can only order in the client tools by a single column - and so I guess the technique I'm looking for is used a lot to produce these types of "order by" columns)
Which was fine when I was referring to a table where Periods where distinct directly but now I have denormalised this for ComponentPeriod so I need something a little more sophisticated Whats the best way to get a sequence with perhaps some partitions in across a subset of distinct columns (I guess from SUMMARIZE or similar)
even though there may be multiple records in ComponentPeriod that have the period 2015 Q4, but I want them all to have the value Sequence value of 1? I've got as far as:
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".
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.
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?
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.
SalesTable:
Customer Product Sales Date Sales First Bike Date
Jon Bike 12/4/2011 $10.00
[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.
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:
Job, Phase of Work, Cost Type, UM, 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.
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 Month/Year June 2015 July 2015 August 2015 September 2015
I am looking for two types of Output (high level and detailed)
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
