Forum Discussion

dliddle's avatar
dliddle
Frequent Visitor
4 years ago
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 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

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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


         

3 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
      dliddle
      Frequent Visitor
      4 years ago

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

  • Shishir22's avatar
    Shishir22
    Solution Sage
    4 years ago

    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.