Donor loyalty calculation

DAX DAX Calculations
12

Hi @Synergetic,

Can you let me know if this performs any better. You can omit the last step, if that is not required, it just retains one record per ID with it’s maximum number of consecutive years.

let
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    AllRecordedDonationsTable_Sheet = Source{[Item="AllRecordedDonationsTable",Kind="Sheet"]}[Data],
    PromoteHeaders = Table.PromoteHeaders(AllRecordedDonationsTable_Sheet, [PromoteAllScalars=true]),
    ChangeType = Table.TransformColumnTypes(PromoteHeaders,{{"Donor ID", Int64.Type}, {"Receipt Amount", Int64.Type}, {"Receipt Date", type datetime}, {"Constuency Codes", type text}, {"Column5", type text}, {"Column6", type text}}),
    ExtractYear = Table.TransformColumns(ChangeType,{{"Receipt Date", Date.Year, Int64.Type}}),
    RenameColumn = Table.Buffer( Table.RenameColumns(ExtractYear,{{"Receipt Date", "Receipt Year"}})),
    GroupRows = Table.Group(RenameColumn, {"Donor ID"}, {{"AllRows", each _, type table [Donor ID=nullable number, Receipt Amount=nullable number, Receipt Year=number, Constituency Codes=text]}}),
    Transformed = Table.AddColumn(GroupRows, "Temp", each
        let
            myTable = [AllRows],
            GroupRows = Table.Group(myTable, {"Donor ID", "Receipt Year"}, {}),
            SortRows = Table.Sort(GroupRows,{{"Receipt Year", Order.Ascending}}),
            AddIndex = Table.Buffer( Table.AddIndexColumn(SortRows, "Index", 0, 1, Int64.Type)),
            AddCustom = Table.AddColumn(AddIndex, "Custom", each 
                try if [Index] <> List.Max(AddIndex[Index]) 
                    then [Receipt Year] = AddIndex[Receipt Year]{[Index]+1}-1 
                    else [Receipt Year] = AddIndex[Receipt Year]{[Index]-1}+1 
                otherwise false),
            NewGroup = Table.Group(AddCustom, {"Donor ID", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table [Donor ID=nullable number, Receipt Year=number, Index=number, Custom=logical]}}, GroupKind.Local),
            ConsecutiveYears = Table.AddColumn(NewGroup, "Consecutive Years", each if [Custom] = true then [Count] else 1, Int64.Type)
        in
            ConsecutiveYears, type table [Donor ID=Int64.Type, Custom=logical, Count=Int64.Type, AllRows=table, Consecutive Years=Int64.Type])[[Temp]],
    ExpandTable = Table.ExpandTableColumn(Transformed, "Temp", {"Donor ID", "Consecutive Years"}, {"Donor ID", "Consecutive Years"}),
    MaxConsecutiveYears = Table.Group(ExpandTable, {"Donor ID"}, {{"Max Consecutive Years", each List.Max([Consecutive Years]), type nullable number}})
in
    MaxConsecutiveYears

Like @BrianJ already mentioned if scheduled this might be acceptable.
I hope this is helpful.

1 Like