How to show only dimension changes

[I_miss_tableau]

Hi, 

I created below crosstab in Power BI:

	Date
Name	Jan	Feb	March
Jan	a	a	a
Kate	a	b	b
Ola	b	b	b
Tom	c	c	c
Mike	c	c	a
Elle	a	b	c
Ann	b	b	b
Ted	b	a	a
Frank	c	c	c
Wes	b	a	a

I want to filter out only employees which changed their team during (at least) last month. Date will be dynamic so I want to avoid typing specific date in formula.

I used this formula (I found it in this forum) but it didnt help. 

Team Changes =

var _CurrentDate=

max('table'[Date])

var _Currenttime=max('table'[Team])

var _Changes=

CALCULATE(max('table'[Team]),'team'[Date]<_CurrentDate,ALLEXCEPT('table','table'[Employee]))

return

if(hasonevalue('table'[Employee]),if(not ISBLANK(_Changes)&&_Currenttime<>_Changes,

_Changes & " to "& _Currenttime

))

[lbendlin · Accepted solution]

First step: bring your data into usable format by unpivoting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRSoTjWJ1oJe/EklSoQBIYgwT9cxB8mFhIfi6QnQzHIDHfzOxUJEGIia45OcgmQlQ65uVhmpiaAuUjnONWlJiXjWFPeGoxqspYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jan = _t, Feb = _t, March = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Jan", type text}, {"Feb", type text}, {"March", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each "1-" & [Attribute] & "-24",Replacer.ReplaceValue,{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", type date}})
in
    #"Changed Type1"

Then you can use Power Query, or the Visual Calculations feature to highlight changes.

[lbendlin · Accepted solution]

First step: bring your data into usable format by unpivoting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRSoTjWJ1oJe/EklSoQBIYgwT9cxB8mFhIfi6QnQzHIDHfzOxUJEGIia45OcgmQlQ65uVhmpiaAuUjnONWlJiXjWFPeGoxqspYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jan = _t, Feb = _t, March = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Jan", type text}, {"Feb", type text}, {"March", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each "1-" & [Attribute] & "-24",Replacer.ReplaceValue,{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", type date}})
in
    #"Changed Type1"

Then you can use Power Query, or the Visual Calculations feature to highlight changes.

[I_miss_tableau]

Thank you but I don't have the same version of PB as you and cannot open your workbook. I tried create the same formula but cannot use PREVIOUS. 

My data has been already unpivoted, pivot was created as visualization. 

[lbendlin · Accepted solution]

Either upgrade to a version that supports Visual Calculations, or implement the logic in Power Query.