locked
How to calculate no of days between FirstDate and LastDate in PowerPivot using DAX function RRS feed

  • Question

  • Hi,

    I have a table in PowerPivot like this:(I have given data for only one caseno, there can be more)

    CaseNo Outcome DateTime
    OS 10/2013 VC 15/1/2013
    OS 10/2013 OM 18/4/2013
    OS 10/2013 AJN 18/1/2013
    OS 10/2013 AJN 22/1/2013
    OS 10/2013 OM 23/1/2013

    I need to get No of Days between MIN date (which is 18/01/2014) and MAX date (which is 18/04/2014) based on this condition OutCome <>"VC".

    I have tried to use this DAX function:

    LastDate:=CALCULATE( LASTDATE ( 'AAA'[DateTime] ),FILTER('AAA','AAA'[OutCome]<>"VC"))

    FirstDate:=CALCULATE( FIRSTDATE( 'AAA'[DateTime] ),FILTER('AAA','AAA'[OutCome]<>"VC"))

    No of days :=[LastDate]-[FirstDate]

    But it's throwing following error:

    Calculation error:A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.

    Kindly assist.

    Thanks,

    SS

    Tuesday, November 11, 2014 4:33 AM

Answers

  • Hi SS,

    I don't seem to get the same error using the test data provided. Do you get the same issue using the following DAX?:

    LastDate:=
    CALCULATE(
      MAX('AAA'[DateTime]),
      FILTER(
        'AAA',
        'AAA'[OutCome]<>"VC"
      )
    )

    FirstDate:=
    CALCULATE(
      MIN('AAA'[DateTime]),
      FILTER(
        'AAA',
        'AAA'[OutCome]<>"VC"
      )
    )

    No Of Days:=INT([LastDate] - [FirstDate])



    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Marked as answer by SS_2011 Wednesday, November 12, 2014 5:34 AM
    Tuesday, November 11, 2014 10:49 AM

All replies

  • Hi SS,

    I don't seem to get the same error using the test data provided. Do you get the same issue using the following DAX?:

    LastDate:=
    CALCULATE(
      MAX('AAA'[DateTime]),
      FILTER(
        'AAA',
        'AAA'[OutCome]<>"VC"
      )
    )

    FirstDate:=
    CALCULATE(
      MIN('AAA'[DateTime]),
      FILTER(
        'AAA',
        'AAA'[OutCome]<>"VC"
      )
    )

    No Of Days:=INT([LastDate] - [FirstDate])



    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Marked as answer by SS_2011 Wednesday, November 12, 2014 5:34 AM
    Tuesday, November 11, 2014 10:49 AM
  • Hi Michael,

    Above DAX worked very well. No error.

    Thanks a lot.

    ~SS

    Wednesday, November 12, 2014 5:34 AM