Add custom column to existing table by offsetting rate column by 1 year

[flyfisher63]

I have table in BI that has all our clients data and I am having a hard time creating a column called Prior Year Rate.

I have the following table except for the Prior Year Rate.

The 1st 3 columns are from the dataset, I added Expected Renewal Date as a calculated column of Expiration Date +1.

I have tried to add the Prior Year Rate but cannot find a way to do it. Its probably a simple solutions that is just escaping me.

Thanks for any help

Policyholder Effective Date Expiration Date Expected Renewal Date Current Year Rate Prior Year Rate ABC Co 1/1/2024 12/31/2024 1/1/2025 56.24   ABC Co 1/1/2025 12/31/2025 1/1/2026 60.35 56.24

[Greg_Deckler]

@flyfisher63 Try this:

Prior Year Rate Measure =
  VAR __PolicyHolder = MAX( 'Table'[PolicyHolder] )
  VAR __Date = MAX( 'Table'[Effective Date] )
  VAR __PreviousDate = 
    MAXX( 
      FILTER( 
        ALL( 'Table' ), 
        [Policyholder] = __Policyholder &&
        [Effective Date] < __Date 
      ), 
      [Effective Date] 
    )
  VAR __Result = 
    MAXX( 
      FILTER( 
        ALL( 'Table' ), 
        [Policyholder] = __Policyholder &&
        [Effective Date] = __PreviousDate 
      ), 
      [Current Year Rate] 
    )
RETURN
  __Return

As a calculated column:

Prior Year Rate Measure =
  VAR __PolicyHolder = [PolicyHolder]
  VAR __Date = [Effective Date]
  VAR __PreviousDate = 
    MAXX( 
      FILTER( 
        ALL( 'Table' ), 
        [Policyholder] = __Policyholder &&
        [Effective Date] < __Date 
      ), 
      [Effective Date] 
    )
  VAR __Result = 
    MAXX( 
      FILTER( 
        ALL( 'Table' ), 
        [Policyholder] = __Policyholder &&
        [Effective Date] = __PreviousDate 
      ), 
      [Current Year Rate] 
    )
RETURN
  __Return

[lbendlin]

Please specify if you want that as Power Query code, as a calculated column, or as a measure.

[samratpbi]

Hi, As @Greg_Deckler mentioned, you can create the measure or a calculated column. In adition, if you need to have previous row value simply in a visual, then you can use Visual Calculation also.

in the visual calculation, you can use PREVIOUS function.

Hope this helps.

If this helps to resolve your problem, then please mark it as solution.

Thanks - Samrat