Forum Discussion
Solved
Difference between rows
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 pr...
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
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.