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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

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