Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Date Difference between two days in same column

Hello,

i have a table with dates and index. i want to find number of dates between 2 days for same index number.

here is the example

"Results" is the output which i want.

last date(max date of an Index) of new index should be 0.  for others, it shoud calculate number of dates between row and next raw date . (i have shown the calculation in 'calculation' column which will give the 'results') i want to get the 'Resutls' column using Index and Actual Date (not the calculation column)

 

IndexActual DateResultsEx: Calculation which need to apply to get Results
18/18/20180(8/18/2018)- (8/18/2018)
18/18/20180(8/18/2018)- (8/18/2018)
18/18/20182(8/20/2018)-(8/18/2018)
18/20/201810(8/30/2018)-(8/20/2018)
18/30/20188etc
19/7/201828 
110/5/20180 
110/5/20180 
28/24/20180 
28/24/20180 
28/24/20183 
28/27/20183 
28/30/20180 
38/18/20180 
38/18/20180 
38/18/20182 
38/20/20180 

 

any help would be highly appriciated 

 

v-juanli-msft  (you have help me for simmilar function)

Need Help

9 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

     

    Please use the measure below:

     

    Measure =

var previndex = MAX(Table1[Index.1])-1

var prevdate = CALCULATE(MAX([Actual Date]),FILTER(ALL(Table1),[Index.1]=previndex))

Return

CALCULATE(DATEDIFF(MAX([Actual Date]),prevdate,DAY))

    Best regards,

    Dina Ye

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you very much for your responce.

      i want to get it as a column (i'll be apply few other operations to that column later)

      i tried to convert it to a column and it given me below error

       

      DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values

       

      what i want is slightly different than your answer(it needs to shift one raw)
      eg: for the date 8/20/2018 value which i need is 10 (8/30/2018-8/20/2018)
      ie: logic shoud be subtract the date from next raw date give the answer to same raw 

      thank you

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Ashish_Mathur 

      this works perfectly. 

      but can you please tell me how to get new index column(Index1) based on other Index?

      • v-diye-msft's avatar
        v-diye-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi ,

         

        You can add the index column in query edit. 

        Add column>index column>from 1

         

        Dina.