Showing Counties that had Changes month to month

[DJSwezey]

I have a table that determines whether or not a county is an Optimal choice for a campaign my company is working on.

I need to create a column/measure that shows if there was an "Optimal" change for the same county in the same state from the previous month. Anything that remains the same be labeled as 'none', from a yes to a no labeled as "Yes to No" and anything from a no to yes labeled as "No to Yes".

Something similar to this: 

The data will continue to change each month as the data source is a folder that contains a new excel file for each months data.

Any help would be greately appreciated!

[v-xinruzhu-msft · Accepted solution]

Hi @DJSwezey 

You can try the following measure

Measure =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( Campaign ),
            [State]
                IN VALUES ( Campaign[State] )
                    && [Country]
                        IN VALUES ( Campaign[Country] )
                            && [Date] < SELECTEDVALUE ( Campaign[Date] )
        ),
        [Date]
    )
VAR b =
    MAXX (
        FILTER (
            ALLSELECTED ( Campaign ),
            [State]
                IN VALUES ( Campaign[State] )
                    && [Country]
                        IN VALUES ( Campaign[Country] )
                            && [Date] = a
        ),
        [Optimal]
    )
RETURN
    SWITCH (
        TRUE (),
        b = BLANK (), "N/A",
        SELECTEDVALUE ( Campaign[Optimal] ) = B, "None",
        b & " " & "to" & " "
            & SELECTEDVALUE ( Campaign[Optimal] )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DJSwezey]

I have tried the following logic, but am getting n/a for everything. 

Optimal Changes = 
VAR CurrentState = 'Campaign'[State]
VAR CurrentCounty = 'Campaign'[County]
VAR CurrentMonth = 'Campaign'[Month]
VAR CurrentYear = 'Campaign'[Year]
VAR PreviousMonth = 
    CALCULATE(
        MAX('Campaign'[Month]),
        FILTER('Campaign', 
            'Campaign'[State] = CurrentState &&
            'Campaign'[County] = CurrentCounty &&
            'Campaign'[Year] = CurrentYear - 1
        )
    )
VAR CurrentOptimal = 'Campaign'[Optimal]
VAR PreviousOptimal = 
    CALCULATE(
        MAX('Campaign'[Optimal]),
        FILTER('Campaign',
            'Campaign'[State] = CurrentState &&
            'Campaign'[County] = CurrentCounty &&
            'Campaign'[Month] = PreviousMonth &&
            'Campaign'[Year] = CurrentYear - 1
        )
    )
RETURN
    IF(
        ISBLANK(PreviousOptimal), "n/a",
        IF(CurrentOptimal = PreviousOptimal, "None",
            IF(CurrentOptimal = "Yes", "No to Yes", "Yes to No")
        )
    )

[jgordon11]

In power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYpMLQaSlvqG+kYGRsZKsTrRSk5AAb98dFFnrKKOMFFDA3QjICajijvjEEcYY4jDGEOsbkESjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [County = _t, Optimal = _t, Date = _t]),
    tbl = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    tbl1 = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
    tbl2 = Table.Group(tbl1, {"County"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index1") }}),
    tbl3 = Table.TransformColumns(tbl2, {{"All", each Table.AddColumn(_, "Change", 
        (x)=> let current = x[Optimal], prior = _[Optimal]{x[Index1] - 1} in try if current = prior then "None" else prior & " to " & current otherwise "N/A") }}),
    tbl4 = Table.Combine(tbl3[All]),
    tbl5 = Table.Sort(tbl4,{{"Index", Order.Ascending}}),
    Result = Table.RemoveColumns(tbl5,{"Index", "Index1"})
in
    Result

[iZainShah]

Works flawlessly 👍