Text Differences from day to day

[gmasta1129]

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 column which shows when the Fund Manager Name changes for a specific investment held by a specific fund.  

For example, the screenshot above shows the following...

fund code "54321" and investment name "ABC Fund", the manager name has changed from BlackRock to Blackstone from September 2nd file to September 1st file (Run Date). 

The new column should show the word "CHECK" so we know that there is a difference in the fund manager names from one day to the next.  Is this possible to do in Power BI?  

[gmasta1129]

@Royel ,

This worked perfectly. Thank you so much! 😀

[Royel]

My pleasure. 

[Royel · Accepted solution]

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]

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"