One of the most useful new additions to DAX in Excel 2016 and the Power BI Designer is the DateDiff() function. It does exactly what you would expect: calculate the amount of time in between two dates, and express that value as either seconds, minutes, hours, days, weeks, months, quarters or years.
Here’s a very simple table of dates:
With this table loaded into the Power BI Designer, you can add new calculated columns to the table by clicking the New Column button on the ribbon. Here are two calculated column definitions that give the number of days and the number of years between the Start Date and the End Date on each row:
[sourcecode language='text' padlinenumbers='true'] DayDurationColumn = DATEDIFF(MyTable[Start Date], MyTable[End Date], DAY) YearDurationColumn = DATEDIFF(MyTable[Start Date], MyTable[End Date], YEAR) [/sourcecode]
The output is pretty much what you’d expect:
It is of course also possible to create measures that use the DateDiff() function to, for example:
[sourcecode language='text' ] YearDurationMeasure = DATEDIFF( FIRSTDATE(MyTable[Start Date]), LASTDATE(MyTable[End Date]), YEAR) [/sourcecode]
All very straightforward, then, and much easier than having to calculate these values yourself.
How would you calculate age when you want to know the age at that point in time? For example let’s assume my birthdate is Dec 1, 1974 and today is July 14, 2015. Using DateDiff and returning the year will calculate my age as 41 when in fact I’m considered 40 until Dec 1. Using DateDif in Excel 2013 produces 40. TSQL will calculate 41. I’d be fine if all MS apps were consistent.
You mean Excel 2016, not Excel 2013, right?
It would be really helpful if you or Alberto, or Marco could post an example of how to use DAX to calculate the number of working hours (9 hour day) between two dates -excluding weekends and holidays … please! I’m new at DAX and under a deadline to get this working. Something like mentioned at http://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/
Thanks,
Mark
In situations like this the best way of solving the problem is to do as much as possible in the model, and as little as possible in DAX. My advice would be to add a column to your Date table containing the number of working hours for each day, and then it would be simple to do your calculation by creating a Sum measure on that column.
Thanks for the quick reply Chris. I need to calculate help desk ticket duration and staff is being evaluated, so it’s important to take into account the ticket start time and end times as well. I’m going to try to get a variant of the formula below from powerpivotpro.com to work … thanks, Mark
Bob Phillips says:
April 30, 2014 at 8:23 am
That didn’t quite come out correctly
=(
CALCULATE(
SUM(Dates[IsWorkday]),
DATESBETWEEN(
Dates[Date],
TwoDates[Column1],
TwoDates[Column2]
)
)
)
– ( WEEKDAY( TwoDates[Column1], 2) 5, 0, MOD( TwoDates[Column2], 1 ) – “08:00:00″*1)
Hello, Chris!
I am faced up with the same problem as Matt described.
For example, if my birthdate is Sep 16, 1993 an now is June 6, 2017, DateDiff(09/16/1993, 06/06/2017, YEARS) returns 24. But as I know, I am 23 now (until Sep 16, 2017).
So, how do you think what is the best way to calculate the age correctly?
I forgot to reply back to this a long time ago. I think I solved this issue with the following calculation:
Employee Age = INT(DATEDIFF(Data[Birth Date], TODAY(), DAY)/365)
The formula got cut off, but here’s the URl for future reference … -Mark
http://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/
I Need to exclude Weekend
Chris I used your syntax, and it seems to have worked. However, when I try to graph it, I get this error.
MdxScript(model) (3,55) Calculation error in measure’aztecaREQUEST'[HOURDURATIONMEASURE]: A date column containing duplicate dates was specified in the call to function ‘FIRSTDATE. This is not supported.
After you have dragged the new column into a graph in Power BI, can you try setting the way it aggregates to “Do Not Summarize”? See https://powerbi.microsoft.com/en-us/documentation/powerbi-service-aggregates/
how to Calculate the no of days between two dates using POWER PIVOT 2013.
Activity Sub_Activity Start END
1 1 10-Oct-2016 15-Oct-2016
2 1 20-Sep-2016 30-Oct-2016
3 2 12-Dec 2016 15-Dec-2016
4 2 10-Dec-2016 13-Dec 2016
I wanted to make summary as
_
Sub_Activity start(min) No_of_days(maximum of end date – minimum of Start date)
1 10-Oct-2016 20 days (30-oct-2016 – 10-oct-2016)
2 10-Dec-2106 5 days (15-dec-2016 – 10-dec-2016)
I am able to do using DAX POWER PIVOT 2016 using DATEDIFF, Please advise how to write using DAX 2013
Hi. Would you happen to know how I could show this data in a table split by Month. So if start date was 01/06/2017, and end date was 05/08/17, a “1 would appear in the table under Jun,Jul,Aug 2017. I can do ,many other things with DAX, but don’t seem to be able to crack this…
Hi Chrish,
I need your help on this below scenarios, We have data in column Date ,A and B.
I need the output like other column “Result”
steps :
A- B = Result
then take value from Result – next row value of B
until the result value get zero or -ve.
once you get zero then need to divide the previous value of result and corresponding vale of B . which i am highlight as red color.
data will be weekly so this calculation will work if i select any week.
so ,this should be in measure. not by calculated column.
Date A B Result
3/1/2019 1000 100 900
2/22/2019 2000 500 400
2/15/2019 15000 200 200
2/8/2019 3000 100 100
2/1/2019 4500 100 0
1/25/2019 2400 1200
2/22/2019 2000 500 1500
2/15/2019 15000 200 1300
2/8/2019 3000 100 1200
2/1/2019 4500 100 1100
1/25/2019 2400 1200 -100
Could you please help on this.
I’m new to Power Query, but I have really enjoyed what I read it can do to eliminate many of the manual steps I’m currently doing within Excel. For instance, I have an Excel spreadsheet that contains data from our phone system. One of the columns is the incoming callers phone number. Typically, this column contains the 10-digit phone number. However, there are times when this column contains a 11-digit phone number. Our SQL data has all our phone numbers stored as a 10-digit number.
I can manually adjust for this within Excel with a simple formula. If the phone number is in column B2, the formula I use is:
=(IF(LEN(B2)>10,RIGHT(B2,10),B2)+0)
Basically, I check the length of the phone number. If it is greater than 10, I take the RIGHT 10-digits, otherwise I take the value in B2. Since the RIGHT function returns a TEXT value, I add a 0 to the returned value so I have a numeric value.
My question for the group is, how can I replicate this using Power Query, so I never need to do this again?
In Power Query:
1) Select your column
2) Go to the Transform tab
3) Click Extract/Last Characters
4) Enter the value 10 and click OK
This keeps just the rightmost 10 characters in a text column. If there are 10 characters then nothing changes; if there are more than 10 you just get the last 10. Is that what you want?
That was a very simple way to extract the last 10 characters. However, the value returned is text. Is there a way to have the value numeric? Actually, what would be really nice is to have the other non-numeric values converted to nulls since a few of the values are “restricted” or “unavailable”.
In the end, I need the 10-digit phone number that is numeric so I can connect it to our SQL data.
The easiest way to do this is to set the data type of the column to be a whole number (by clicking on the appropriate icon in the top left-hand corner of the column), then replacing any resulting error values with null
I tried that, but how do I replace the errors with nulls?
If you right-click on the column there’s a Replace Errors menu item. Just enter null into the dialog and the errors will be replaced with null values, not the text “null”
start date is 01 Jan 2019 & end date is 31 Dec 2019, By using datediff over month as interval it gives 11 which is not correct.Solution ASAP
I get the same answer when I use the DateDiff function in SQL, so I suspect it is working as expected