Kasper de Jonge BI Blog

I wanted to get some information on a importing process where i load different files into a database. I don’t have access to the database i only get CSV export files from the admins.

I have two date fields where i want to see the difference between them in seconds (should only take seconds). In SQL Server i would have used DateDiff function and be done with it. No such function is available in DAX so we have to be creative ourselves. I loaded the CSV into PowerPivot and decided to add new measurs in PowerPivot window.

The first solution i came up with was to try and subtract one date from the other, this seemed to work. So now i wanted to get only the  seconds of this subtraction:

=SECOND(<datefield1> – <datefield2>)

This was the result i hoped for.

But being a developer I wasn’t satisfied with this result, when the process would run for more than 60 seconds the minute would be ignored, so you could make it into:

=SECOND(<datefield1> – <datefield2>) + (MINUTE(<datecolum1> – <datecolum2>)*60)

But again what happens when the process runs more than 60 minutes.. after a great discussion on twitter with Vidas on how to solve this problem, he came up with (thanks to Marius Dumitru (MSFT))

86400. * ( [DateField1] – [DateField2] )

First number because: (24*60*60 = 86400 seconds per day)

This is a pretty rock solid formula that would give you the difference in seconds between two dates regardless the interval.

One conclusion Vidas and I got to was when a serious challenge arises PowerPivot is not really suited for a fast solution. You would be better off fixing it in your source or be very creative like the solution above. Maybe the default Excel user is better suitable for these challenge because of their experience with Excel.Or  maybe PowerPivot needs a different mindset than the defensive programming we developers are used to do, trying to be prepared for all the possible exceptions, and rely on assumptions (like the time difference is always seconds) and be done with it.

Ofcourse in the future we will have new functions, this will make our work easier.

Vidas Matelis has been twittering live from the DAX session at the SQLPass. I just wanted to share these great new function available in DAX:

• Data Analysis Expressions = DAX
• DAX lets user do multidimensional analysis without user knowing that this is multidimensional analysis
• sample DAX: =[Qty]*[Price] – syntax just like Excel.
• DAX is not replacement for MDX
• DAX provides functions that implement relations database concepts: Filter tables, aggregates, follow relationships
• one of the many sample: =SUMX(RELATEDTABLE([Sales], Sales[Amount]) DAX
• dax has functions to assist with dynamic aggregations of measures: dynamic sample: =VALUES(Time[year]) & “.” & VALUES(Product[ProductID])
• DAX sample: =IF(VALUES(Time[Year])=2008,”Baseline”,”normal”)
• More than 80 Excel functions in DAX
• CTP3 has new FORMAT function to allow to convert any number to string
• DAX sample: [salesAmt]/[SalesAmt](All(Product)) – use measure as function.
• DAX = Sales[SalesAmt]/CALCULATE(Sales[SalesAmt], ALL(Product)) more complex syntax
• CTP3 DAX has 35 Time Intelligence Functions – require date column in the data. Column type Date
• DAX this version does not have custom time periods and weeks. Works with Yr, Qtr,Mth,Day
• DAX: FirstDate, LastDate, FirstNonBlank, StartOfMonth, StartOfQtr, EndOfYear
• DAX: DateAdd, DatesBetween, DatesInperiod – 26 function that return a table of dates
• DAX: PreviousDay, Nextmonth, DatesMTD, DatesYTD, TotalMTD, OpeningBalanceQuarter, ClosingBalanceYear, etc <-more funct
• Year over year growth: =Sales[SalesAmtt]-Sales[SalesAmth)(DateAdd(Time[Date],-1,Year].All(Time))
• AllTime – need to add now in CTP3, not in RTM(will be under cover). So that first year has data.
• QTD Sales =TotalQTD(Sales[SalesAmt],Time[Date], All(Time))
• DAX yearAgo:=Sales[SalesAmt)(ParallelPeriod(Time[Date],-12, Month).All(Time))
• autocomplete in CTP3 add single quotes, but they are optional.

Wow i can’t wait to start playing with these new  functions ! Thanks Vitas for sharing the info !

I also really really hope these time functions will be available in SSAS in the future, they won’t be availble in R2 Donald Farmer told me.

update: What will happen to the time functions when using a SSAS source? Is a Date dimension ok ? I hope so !