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
ADVERTISEMENT
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
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:
=CALCULATE(
sum(Sales[Revenue]);
(DatesMTD(Time[date]))
)
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
May 13, 2015
I have a formula that should use data from 2 slicers:
Spend per period (changing currencies & dates):=[Spend per period]*CALCULATE([Sum of Value],FILTER(Currency,Currency[Date]=[End Date]),FILTER(Currency,Currency[Attribute]=CurrencySlicer[Attribute]))
I managed to link the [End Date] from the slicer to the formula, however the [Attribute] field is not numeric so I can't duplicate the same methodology.
{FYI: End Date:=LASTDATE('Finish Date Slicer'[Column1]) }
I assume that I need to build a formula to extract the data chosen in the slicer, and can't connect it directly to the slicer.
View 2 Replies
View Related
Aug 4, 2015
PowerPivot 2013. In this example I am trying to get revenue per employee (highlighted in yellow)
In order to do that I need the numerator to appear in every row (red arrow in subtest)
I thought this would do the trick, but ...
=CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Employee Name]))
View 7 Replies
View Related
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
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
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
View Related
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
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
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
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
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
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.
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.
View 3 Replies
View Related
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:
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.
View 8 Replies
View Related
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
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
View 4 Replies
View Related
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
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.
View 2 Replies
View Related
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
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
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
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
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.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
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
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
July
August
September
October.
View 3 Replies
View Related
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
[code]....
View 2 Replies
View Related
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
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
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.
Details:
Value=40
Type=Type
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
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
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
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
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
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
Jul 16, 2015
I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?
let
FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",
[Code] ....
View 8 Replies
View Related
