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 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?  

 

 

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!

4 Replies

  • Royel's avatar
    Royel
    Super User
    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"