DAX - difference between dates

[LRMMF]

I need your help , i need to calculate difference between dates, with result in days.

so i have a factorder with sk_date2 and sk_date1, both of them FK's to a Dim Date (but relationship not established in PP), FK has this format 20120701 (yyyymmdd) and in this table date 01-07-2012 00:00:00

when dates aren't defined they have -1. I've also noticed that i can make the difference immediatly using the keys, which simplifies the calculation.

so i have to do something like:


test:=CALCULATE
(('Fact Order'[sk_date2] - 'Fact Order'[sk_date1]); ('Fact Order'[sk_date2]>=1); ('Fact Order'[sk_date1]>=1))
/
CALCULATE(COUNTROWS('Fact Order'); 'Fact Order'[sk_date2]>=1; 'Fact Order'[sk_date1]>=1)


Questions:
1- This has an syntax error. Probably i can't write the numerator as i did.
2- How would you write this query?
3- i can create a new column with the difference between dates, and them sum it up and divide by the total, but my excel file is growing big and i would like to avoid new columns. Adding new columns contribute to size and, in some way, to the complexity of the powerpivot model, right?
4 - if i want to include the lines where some date is not defined (and use this "unfinished" orders in calculation), is there anyway to substitute -1 with today's date and simulate the FK?
For ex. order 1234 has sk_date2 = -1, and i force the calculation to use (as i am on 06-03-2014) 20140306 and make the difference to sk_date1 ...?

Regards

 

[scottsen]

Yes, adding calculated columns adds to filesize and reduces perf. How many rows you dealing with? (I mean, if its < 100k... I just wouldn't worry about it).

So, you are trying to write [Total Date Delta] / [Total Orders] -- the average delta between the dates?

Let's see if we can't do that. Your biggest problem is that this part:

CALCULATE (('Fact Order'[sk_date2] - 'Fact Order'[sk_date1])

doesn't have an aggregate or similiar. You are trying to operate on like... 1 row, but you have no row context. That's where the X functions come in. I suspect your denominator was just fine.

Also note you can probably just use AVERAGEX instead of my SUMX to get the average, instead of dividing by the number of rows - but I'm only 66% confident on that


Total Date Delta := SUMX('Fact Order', 'Fact Order'[sk_date2] - 'Fact Order'[sk_date1])

(And we can wrap that in your calculate to strip off rows you hate). That is valid because the SUMX creates an iterator... we walk 1 row at a time doing that per-row-expression, then adding up all the results.

Not I also kinda suspect this won't work as is, because those dates are in weird format 20120701 ... which will return like, 10000 if something is 1 year apart. You might be able to do a lookup back to your calendar table to get "real" dates? But... I am kinda thinking you should convert those columns into real dates to avoid this madness.

On your last question... I don't see why you couldn't use an IF('Fact Order'[WhateverDate] = -1, TODAY(), 'Fact Order'[WhateverDate] = -1) ... though, it certainly going to slow down the measure.