Difference between rows

[dliddle]

Morning 

I have been struggling to get anything that works for calculating the change between two dates for a KPI measure, I want to show the change from the most current report date to the previous report date (e.g Overdue defects would be -2 for last report).  I've tried several ways without success so I've tried creating a table of the values as below and then added a step to work out the previous date to use that to force it, however when ive created the previous date column its only calculated for a few rows and now im wondering if there is some king of formatting issue i've missed that is preventing me doing the calculations ive tried?

Any help would be appreciated

[Shishir22]

Hello @dliddle ,

In Power Query, sort the data as per date asc and add index column-

After creating Index Column, please try below dax for calculated column-

PreviousDate =
VAR _PrevIndex = 'TIGER Table'[Index] - 1
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'TIGER Table'[Report Date], 0 ),
        FILTER ( ALL ( 'TIGER Table' ), 'TIGER Table'[Index] = _PrevIndex )
    )

Please mark it as answer if it resolves your issue. Kudos are also appreciated.

[SpartaBI · Accepted solution]

@dliddle you are using a time intelligence function on a date that is not a proper date column. You can't get the result if it's not in your date column. 
So:
1. Never use time intelligence on columns that are not a date column in a proper date table.
2. If what you want is to subtruct 7 days from a date than write this calculated column instead:
'Tiger Table'[PreviousDate] = 'Tiger Table'[Report Date] - 7
3. If what you want is to get the previous date in that table you can write this calculated column:

'Tiger Table'[PreviousDate] = 
VAR _current_date = 'Tiger Table'[Report Date]
VAR _result = 
	MAXX(
		FILTER(
			'Tiger Table',
			'Tiger Table'[Report Date] < _current_date
		),
		'Tiger Table'[Report Date]
	)
RETURN
	_result

Showcase Report – Contoso By SpartaBI

     

[dliddle]

This did it thank you.  This is actually perfect as another table the report dates are not consistently 7 days apart.