Forum Discussion

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi v-yuta-msft,

         

        Appreciate your sharing.

         

        However, when I used measure for Index By Q No, I have multiple tables. I have trouble finding EARLIER function in my table and "Dense" is gray out. Refer photo screenshot:-

         

        Once I solve the above Index By Q No, only can I used measure for DateDiff.

         

        Let me know your thoughts

         

        Thank You