Forum Discussion

9 Replies

  • Thejeswar's avatar
    Thejeswar
    Super User
    8 years ago

    Hi Anonymous,

    DateDiff should be used as a Column, not a measure

     

    Difference = DATEDIFF(Table1[Last Modified], TODAY(), DAY)

    If this solves, accept the solution and give a kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thejeswar

       

      Your measure does not give an accurate result. See the screenshot below.

      I need the first row in the (Diffrence Collumn) of every new Q No to be empty; start of new start stage change, just like my ealier posting !

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        8 years ago

        Hi BDA2,

         

        To achieve your requirement, firstly you need to create a calculate column [Index By Q No]  to set index for every group using DAX below:

        Index By Q No = RANKX(FILTER(Table1, Table1[Q No] = EARLIER(Table1[Q No])), RANKX(ALL(Table1), Table1[Last Modified]), , DESC, Dense)

        Then create a calcualte column to calculate datediff which is your expected result:

        DateDiff = 
VAR Previous_Date = CALCULATE(MAX(Table1[Last Modified]), FILTER(Table1, Table1[Index By Q No] = EARLIER(Table1[Index By Q No]) - 1 && Table1[Q No] = EARLIER(Table1[Q No])))
RETURN 
DATEDIFF(Previous_Date, Table1[Last Modified], DAY)

         

         

        Hope it's helpful to you.

         

        Jimmy Tao