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


ADVERTISEMENT

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.
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

Transact SQL :: Convert Local Date Time Field To GMT?

Jul 28, 2015

I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]

[URL]

View 5 Replies View Related

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Aug 7, 2007



Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View 5 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 :: 28 Day Rolling Averages With Discrete Time Attributes

Jul 14, 2015

I've been able to find plenty of info online in relation to calculating moving averages for a given value. I am having a specific problem however.

So because I need to see the average on a daily basis, my forumla (below) works fine:

However, my Calendar dimension (TIME) also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.) which has no reference to the year.

I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.

So you could have a line chart where the X axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.

But when I replace DATE with DateMonth in my pivot, the calc gives some strange numbers.

Rolling28DayAvg :=
CALCULATE (
    [Revenue],
    DATESINPERIOD (
        TIME[DATE],
        LASTDATE ( TIME[DATE] ),
        -28,
        DAY
    )
)
    / 28

View 15 Replies View Related

Power Pivot :: Sum For Events And Time - Calculate Total Ratio

May 22, 2015

I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).

In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.

For instance
                            201501 201502 201503 TOTAL
Number of events           8         10        10     28
Time                             5           5         
4     14
Ratio                            8/5       10/5     10/4  28/14

View 3 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 :: 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 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 :: Measure To Count All People Who Have Been Employed Since Start Of Time?

Jul 29, 2015

Say that I have employee data for 2010-2015. I have a pivot table that displays employee data for all the different months in 2015 I want each month to show all people who have been employed since the start of time until and including that month. So

for January 2015 it should show all people employed from January 2010 to January 2015.
for February 2015 it should show all people employed from January 2010 to February 2015.
for March 2015 it should show all people employed from January 2010 to March 2015.

How can I write a measure for this? I have a date table set up.

View 2 Replies View Related

Power Pivot :: Calendar Look Up Table - Custom 24 Hour Start Time

Jul 9, 2015

I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as  the last parameter, I would choose true().

I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table. 

View 6 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:

=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

Power Pivot :: Return Time Based Upon Sum Of Personnel Arriving At Fire Scene

Sep 10, 2015

As part of analyzing our fire department's response times, we measure the time it takes to get a certain amount of people on a fire scene. I have two table that contains incident data. The incident table contains one row per incident. The apparatus table contains multiple rows per incident with each row contain the information for each unit. They are connected by the incident key with a one to many relationship. 

The column apparatus.num_personnel contains the count of personnel for each fire truck. The goal is to capture the response time of the unit that is carrying the 10th person to the scene. In the example below, E78 was the unit that the sum of apparatus.num_personnel was => 10. There response time of 0:06:18 is returned to the incident table. 

I realize I will need to create a logical test (calculated column on the incident table) that sums the number of personnel for each incident and then when that number => 10, then return the MIN Response Time of that row. From past projects, I understand I can create a temporary table (ADD COLUMNS) which will iterate over each incident row, comparing the units for that incident. I"m just not sure how to put it all together. 

Overview of data model. The incident and apparatus are my data tables and the rest are lookup tables. The parameter table is used for a percentile measure, but doesn't play a role with this project. 

View 9 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.

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

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:

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

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
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

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

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

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

Convert UTC To Local Time

Aug 1, 2014

I have a stored procedure, it is accepting a parameter which is in local datetime format. But the front end application is passing UTC date format. How I can convert UTC date time to local date time...

View 2 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.

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

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
July
August
September
October.

View 3 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 :: 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

[code]....

View 2 Replies View Related

Convert UTC Time To Local Time

Dec 29, 2005

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

 

Thanks for your help.

 

 

View 13 Replies View Related







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