locked
Lifetime measure RRS feed

  • Question

  • Hey,

    I have to columns with a fromdate and todate. The fromdate = 01-01-2015 and the todate = '01-01-9999' and I like to count the number of days between those to dates, but if the todate > today then it have to use the today date instead. Is that possible? I dont want to use a second column.



    • Edited by Daniel Dahl Wednesday, May 20, 2015 9:23 AM
    Wednesday, May 20, 2015 9:22 AM

Answers

  • Hi Daniel,

    You can use a combination of the TODAY and INT functions to achieve this.

    If you want to create this as a Calculated Column then you can try something like this:

    =
    IF(
      'TableName'[todate] > TODAY(), 
      INT(TODAY()) - INT('TableName'[fromdate]),
      INT('TableName'[todate]) - INT('TableName'[fromdate])
    )

    If you want to create a Calculated Field then you can try something like this:

    SumOfDifferenceInDays:=
    SUMX(
      'TableName', 
      IF(
        'TableName'[todate] > TODAY(), 
        INT(TODAY()) - INT('TableName'[fromdate]),
        INT('TableName'[todate]) - INT('TableName'[fromdate])
      )
    )

    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, Twitter: @nimblelearn

    • Marked as answer by Daniel Dahl Wednesday, May 20, 2015 12:48 PM
    Wednesday, May 20, 2015 12:22 PM

All replies

  • Hi Daniel,

    You can use a combination of the TODAY and INT functions to achieve this.

    If you want to create this as a Calculated Column then you can try something like this:

    =
    IF(
      'TableName'[todate] > TODAY(), 
      INT(TODAY()) - INT('TableName'[fromdate]),
      INT('TableName'[todate]) - INT('TableName'[fromdate])
    )

    If you want to create a Calculated Field then you can try something like this:

    SumOfDifferenceInDays:=
    SUMX(
      'TableName', 
      IF(
        'TableName'[todate] > TODAY(), 
        INT(TODAY()) - INT('TableName'[fromdate]),
        INT('TableName'[todate]) - INT('TableName'[fromdate])
      )
    )

    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, Twitter: @nimblelearn

    • Marked as answer by Daniel Dahl Wednesday, May 20, 2015 12:48 PM
    Wednesday, May 20, 2015 12:22 PM
  • Beautiful - thank you so much.
    Wednesday, May 20, 2015 12:49 PM
  • DECLARE @YourTable TABLE (
    	fromdate date,
    	todate date
    )
    INSERT INTO @YourTable VALUES
    ('01-01-2015', '01-01-9999')
    
    SELECT 
    	  fromdate
    	, todate
    	, CASE 
    		WHEN DATEDIFF(DAY, GETDATE(), todate) > 0 THEN DATEDIFF(DAY, fromdate, GETDATE()) 
    		ELSE DATEDIFF(DAY, fromdate, todate) 
    	  END AS NumberOfDays
    FROM @YourTable


    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 20, 2015 12:50 PM
    Answerer
  • Glad I could help :)

    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, Twitter: @nimblelearn

    Wednesday, May 20, 2015 5:38 PM