Calculate Date Difference between two days in same column

[Anonymous]

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)

Index	Actual Date	Results	Ex: Calculation which need to apply to get Results
1	8/18/2018	0	(8/18/2018)- (8/18/2018)
1	8/18/2018	0	(8/18/2018)- (8/18/2018)
1	8/18/2018	2	(8/20/2018)-(8/18/2018)
1	8/20/2018	10	(8/30/2018)-(8/20/2018)
1	8/30/2018	8	etc
1	9/7/2018	28
1	10/5/2018	0
1	10/5/2018	0
2	8/24/2018	0
2	8/24/2018	0
2	8/24/2018	3
2	8/27/2018	3
2	8/30/2018	0
3	8/18/2018	0
3	8/18/2018	0
3	8/18/2018	2
3	8/20/2018	0

any help would be highly appriciated 

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur 

this works perfectly. 

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

[Ashish_Mathur]

Hi,

Good to know that.  Click on Home > Edit Queries and see the steps there.

[Anonymous]

@Ashish_Mathur 

thank you. but this gives a index for all raws. 
but here you got an index1 with respect to original index (separate index1 for index number 1 and separete index for index number 2)

[Ashish_Mathur]

Hi,

Look at the Partition step there.  That line has to be manually written.  It is because of this step that different index numbers are being assigned.

[Anonymous]

---

@Ashish_Mathur wrote:

Hi,

Look at the Partition step there.  That line has to be manually written.  It is because of this step that different index numbers are being assigned.

---

Thank you very much. i'll check

[v-diye-msft]

Hi ,

You can add the index column in query edit. 

Add column>index column>from 1

Dina.

[v-diye-msft]

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]

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