Difference Between Rows, Grouped by Column

[Anonymous]

Hello,

I am trying to retrieve [Days Between Incidents], grouped by the [Division]. Currently, I have [Days Between Incidents], without incorporation of the [Division]. I would like to be able to filter by Division for the report I create. I am having trouble with that, especially because I do not know the best solution.. whether to use measures or calculated columns.

This is what I currently have available:

Tables

Incidents - Shows Incident #, Date of the Incident, Date of Incident Filtered, and Division.

• [Date of Incident Filtered] is a measure created based on required filters on [Date of Incident]
• [Division] is retrieved from another table that is joined by a text Key (that was the only way)

Previous Incident

• [Date] is a calculated column of dates created with function CALENDAR(FIRSTDATE('Incidents'[Date of Incident]),TODAY())
• [Date of Incident Filtered] is retrieved from Incidents table.
• [Previous Incident Date] is a calculated column for the previous incident date (filtered), in reference to [Date]
  Previous Incident Date =

  VAR LastNonBlankDate =

  CALCULATE (

  LASTNONBLANK ( 'Previous Incident'[Date], 1 ),

  FILTER (

  ALL ( Previous Incident' ),

  'Previous Incident'[Date]<= EARLIER ( 'Previous Incident'[Date] )

  && NOT ( ISBLANK ( 'Previous Incident'[Date of Incident Filtered] ) )

  )

  )

  RETURN

  CALCULATE (

  LASTDATE( 'Previous Incident'[Date of Incident Filtered] ),

  FILTER ( ALL ( 'Previous Incident' ), 'Previous Incident'[Date of Incident Filtered] = LastNonBlankDate )

  ) 

DaysBTIncidents - Days Between Incidents, according to the Fiscal Date. This is the visualization to be shown.

• [Days Between Incidents] is a measure:
  Days Between Incidents =

  CALCULATE(

  DATEDIFF(

  MAX('Previous Incident'[[Previous Incident Date]),

  MAX(FiscalCalendar[Date]),

  DAY

  ),

  USERELATIONSHIP(FiscalCalendar[Date],

  'Previous Incident'[Date]

  )

  )

FiscalCalendar - this table contains the Fiscal Dates, containg information such as Fiscal Month, Fiscal Year, Holiday Days, etc.

Relationships

• Incidents[Date of Incident] to FiscalCalendar[Date] (not shown) - Many to One - Single
• Incidents[Date of Incident] to Previous Incident[Date] (in pink) - Many to One - Single 
• Incidents[Division Key] to Divisions[Division Key] (not shown) - Many to One - Single
• Previous Incident[Date] to FiscalCalendar[Date] (inactive) - Many to One - Single

I would like to obtain the [Days Between Incidents] measure, incorporating the Incidents[Division]... that means, for every division, there should be a new counter. Right now, when I filter by [Division], the numbers are the same for each, since the Previous Incident Date has no specified reference to the Division. What is the best way to do this? Are there DAX formulas that can be placed into a measure, optimizing performance? Or should I create a new table with calculated columns, that has every calendar date, and previous incident date per division?

Would really appreciate your advice!!

[v-shex-msft · Accepted solution]

H @Anonymous,

You can try to use below measure to calculate previous incident date based on current incident index and division group:

Previous Incident Date =
VAR _currIncident =
    SELECTEDVALUE ( 'Previous Incident'[Incidents] )
VAR _prevIncident =
    CALCULATE (
        MAX ( 'Previous Incident'[Incidents] ),
        FILTER (
            ALLSELECTED ( 'Previous Incident' ),
            'Previous Incident'[Incidents] <= _currIncident
        ),
        VALUES ( 'Previous Incident'[Division] )
    )
RETURN
    CALCULATE (
        MAX ( 'Previous Incident'[Date] ),
        FILTER (
            ALLSELECTED ( 'Previous Incident' ),
            'Previous Incident'[Incidents] = _prevIncident
        ),
        VALUES ( 'Previous Incident'[Division] )
    )

If above not help, please share some sample data for test and coding formula.

Regards,

Xiaoxin Sheng