Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
11 months ago
Solved

Text Differences from day to day

Hello,   I have a file that runs Mon-Fri which contains the following columns   1. Fund Code 2. Run Date 3. Investment Description 4. Fund Manager Name     I would like to create a co...
Need Help
  • Royel's avatar
    Royel
    11 months ago

    Hi MasonMA  Here is an alternate solution using dax 

     

    Use a calculated column 

    Manager Change Check = 
VAR _CurrentFundCode = Data[Fund Code]
VAR _CurrentInvestment = Data[Investment Description]
VAR _CurrentDate = Data[Run Date]
VAR _CurrentManager = Data[Fund Manager Name]

VAR _PreviousDate = 
    CALCULATE(
        MAX(Data[Run Date]),
        FILTER(
            ALL(Data),
            Data[Fund Code] = _CurrentFundCode &&
            Data[Investment Description] = _CurrentInvestment &&
            Data[Run Date] < _CurrentDate
        )
    )

VAR _PreviousManager = 
    CALCULATE(
        MAX(Data[Fund Manager Name]),
        FILTER(
            ALL(Data),
            Data[Fund Code] = _CurrentFundCode &&
            Data[Investment Description] = _CurrentInvestment &&
            Data[Run Date] = _PreviousDate
        )
    )

RETURN
IF(
    _CurrentManager <> _PreviousManager && 
    NOT(ISBLANK(_PreviousManager)),
    "CHECK",
    BLANK()
)

     

    Output: 

     

    Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

MasonMA's avatar
MasonMA
Super User
11 months ago

HI gmasta1129 

 

Try this M code (adjust the hard-coded Names if you have to), 

let
    Source = YourTable,
    #"Sorted Rows" = Table.Sort(Source, {
        {"Fund Code", Order.Ascending}, {"Investment Description", Order.Ascending}, {"Run Date", Order.Ascending}
    }),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Previous Table" = Table.TransformColumns(#"Added Index", {{"Index", each _ + 1, Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(
        #"Added Index",
        {"Fund Code", "Investment Description", "Index"},
        #"Previous Table",
        {"Fund Code", "Investment Description", "Index"},
        "Previous",
        JoinKind.LeftOuter
    ),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Fund Manager Name"}, {"Previous Manager"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Previous", "Manager Change", each 
        if [Fund Manager Name] <> [Previous Manager] and [Previous Manager] <> null then "CHECK" else null
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Previous Manager"})
in
    #"Removed Columns"