locked
Days between 2 dates RRS feed

  • Question

  • Hi

    I have been trying to retrieve the number of days between two dates. It should be an easy task, but it shows me error.

    I tried simply by doing : date1-date2

    and:

    testmeasure:=DAY([Shipment Date])-DAY([Due Date])-->thisone says that cannot know the value for shipment date but if i change the order it says the same for due date, some it doesnt seem the column. both are datetime

    also using:

    datesbetween(DimDate[Date_PK];date1field;date2field)

    and it didnt work.

    any ideas¿

    thanks!


    • Edited by the_txeriff Monday, December 3, 2012 5:47 PM
    Monday, December 3, 2012 5:38 PM

Answers

  • Apologies.  Didn't catch that you were doing this in a measure.  The calc above works fine in a row context for a calc column.

    Simplest approach is probably with MAXX at the row level:

    =MAXX(SalesOrder,SalesOrder[DueDate] - SalesOrder[Shipment Date])

    Catch with that is if you have multiple rows for the date you are slicing by, it will just take the max of the diffs.

    Another approach is to take the last date within a given context and lookup the associate DimDate sequence.  You can wrap a LASTDATE() around the date columns.  With the 2 dates, easiest way is to have a dedicated date table for the second date.  And then create the following measures:

    LastExecDateSeq:=CALCULATE(MAX(DimDate[Sequence])
                           ,FILTER(ExecutionLog,LASTDATE(ExecutionLog[ExectutionDate]))
                          )
    
    LastDate2Seq:=CALCULATE(MAX(DimDate2[Sequence])
                            ,FILTER(ExecutionLog,LASTDATE(ExecutionLog[Date2]))
                     )
    
    DateDiffExecDate2:=[LastExecDateSeq] - [LastDate2Seq]

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, December 3, 2012 8:35 PM
    Answerer

All replies

  • datediff(dd, date1field, datefield)

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Monday, December 3, 2012 5:40 PM
  • this is not sql server, there is no datediff in dax


    • Edited by the_txeriff Monday, December 3, 2012 5:41 PM
    Monday, December 3, 2012 5:40 PM
  • Best approach is to use a sequence number in your date dimension and simply subtract on those.  Sequence starts at zero for your first date and simply increments by  for each subsequent date.  Then via your relationships, you can lookup the sequence for each date and use in the subtraction.

    Can also use the following:

    =COUNTROWS(DATESBETWEEN(DimDate[Date],TableName[Date1],TableName[Date2]))-1
    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, December 3, 2012 5:51 PM
    Answerer
  • It shows same error. I tried that before.

    For what is the last "-1"?

    my calculation:

    Test1:=COUNTROWS(DATESBETWEEN(Dates[PK_Date];'SalesOrder'[Due Date];'SalesOrder'[Shipment Date]))-1

    it says that due date cannot be calculated for some reason in the error

    Monday, December 3, 2012 5:54 PM
  • Apologies.  Didn't catch that you were doing this in a measure.  The calc above works fine in a row context for a calc column.

    Simplest approach is probably with MAXX at the row level:

    =MAXX(SalesOrder,SalesOrder[DueDate] - SalesOrder[Shipment Date])

    Catch with that is if you have multiple rows for the date you are slicing by, it will just take the max of the diffs.

    Another approach is to take the last date within a given context and lookup the associate DimDate sequence.  You can wrap a LASTDATE() around the date columns.  With the 2 dates, easiest way is to have a dedicated date table for the second date.  And then create the following measures:

    LastExecDateSeq:=CALCULATE(MAX(DimDate[Sequence])
                           ,FILTER(ExecutionLog,LASTDATE(ExecutionLog[ExectutionDate]))
                          )
    
    LastDate2Seq:=CALCULATE(MAX(DimDate2[Sequence])
                            ,FILTER(ExecutionLog,LASTDATE(ExecutionLog[Date2]))
                     )
    
    DateDiffExecDate2:=[LastExecDateSeq] - [LastDate2Seq]

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, December 3, 2012 8:35 PM
    Answerer
  • Thanks Brent.

    I tried the first DAX and it works. I will keep in mind the explanation.

    I think I should make the calculation in column and then use it for a calculatedMeasure.

    Tuesday, December 4, 2012 8:44 AM