checking the property of last row by time

greg lipman 61 Reputation points

the cube I have for a number of reasons is a non-aggregated table of transaction rows for insurance policies
like date 1 new business date 2 endorsement date 3 cancel etc etc
(with all the dimensions like dates policy effective and expiration and accounting date (transaction date)
number of reasons to do it that way not important at the moment

what I need is to filter only policies where the LAST transaction by some DATE (dim accounting date)
is not CANCEL (dim transaction type)
and then to aggregate for those

in other words the reports asks for a count of active (not cancelled) policies on a certain date plus aggregated measures like written and earned premium

important that the CANCEL MUST be the last by that DATE not followed by reverse (Reinstatement)

tricky huh? I tried filter and tail cannot find anything close enough


SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,258 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points

    Judging by the 'tail' reference, this is an MD question. The recommended MD way of solving such problems is to create an attribute in the Policy dimension that will hold pre-calculated, at processing time, information on whether the policy's last transaction was CANCEL.

    0 comments No comments

  2. greg lipman 61 Reputation points


    the time T at which we take the count can be (and will be most often) in the PAST!!!!
    so the attribute will not reflect the state of a policy at that moment!

  3. greg lipman 61 Reputation points


    yes it needs to be selectable the moment is time for report

    actually somebody here suggested an ingenious solution
    we create a calculated measure 1 for Cancel and -1 for Reinstatement
    use where or subselect for a time slice and we filter on that measure
    if it is > 1 than Cancel was not followed by reinstatement!
    the problem is I cannot make it work :( my technique is bad not my area
    here it is and filter is in pseudo-code even

    MEMBER [Measures].[Cancel Counter] AS
    IIf([t Bd Transaction Types].[Type Cd] = 'Cancel', 5,
    IIf([t Bd Transaction Types].[Type Cd] = 'Reinstatement', -1,0)
    {[Measures].[Cancel Counter]} on COLUMNS,
    NON EMPTY [t Bd Transaction Types].[Type Cd].allmembers*[Dim Policy].[Policy Nbr].allmembers ON Rows
    //!!!! filter {
    // sum([Measures].[Cancel Counter]) = 0
    // }
    FROM [Bridger Olap]
    where [Eff Date].[Full Date] < '10/5/2022'
    and [Exp Date].[Full Date] > '10/5/2022'

    !!!!!!!!!!! for some reason the counter is 0 for Cancel although I see them !!!!!!!!!!!!!!!!!!
    please help

  4. greg lipman 61 Reputation points

    I set Cancel to 5 in example just to make sure I am not crazy
    it should be 1 as I explained to be matched by reverses/reinstatements

    0 comments No comments

  5. greg lipman 61 Reputation points

    here is the latest code below
    neither Cancel Counter not filter in where clause work
    all cancel counter's are 0s i know there are last cancels
    why?????????????? pls help
    and in the where filter does not filter :) does nothing!

    MEMBER [Measures].[Cancel Counter] AS
    IIf([t Bd Transaction Types].[Type Cd].currentMember.membervalue = "Cancel", 1,
    IIf([t Bd Transaction Types].[Type Cd].currentMember.membervalue = "Reinstatement", -1,0)
    {[Measures].[Cancel Counter]} on COLUMNS,
    [Dim Policy].[Policy Nbr].members ON Rows
    FROM [Bridger Olap]
    where filter([Txn Time].[All].Children,
    [Txn Time].[Full Date] < CDate('10/06/2022'))