Comparing changes in Attributes based on 2 columns from Month to Month

[tomekm]

Hello,

I'm trying to create a calculated column or measure that will identify the same Attribute from Period 1 to Period 2, and see if the Attribute preserves the same ID or not. This table will clarify my problem:

ID	Attribute	Period
1	AAA	P01
2	BBB	P01
3	CCC	P01
5	AAA	P02
2	BBB	P02
3	CCC	P02
5	AAA	P03
67	BBB	P03
9	CCC	P03

In this example, if comparing period P01 to P02, the only change is AAA (going from ID 1 to ID 5). If comparing period P02 to P03, the changes are BBB (from ID 2 to 67) and CCC (from ID 3 to 9). I'd like to somehow create 2 Period filters for start and end, and based on the Period selections, a column showing that the specific attribute has had a change (T/F type column) in the ID number for the same Attribute between the 2 periods I selected.

Is this possible?

Thank you.

[v-jianboli-msft · Accepted solution]

Hi @tomekm 

Please try:

First create a new table for slicer:

Use the two columns to create two slicer 

Then apply the measure to the table visual:

Measure = 
var _a = CALCULATE(MAX('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Period]=SELECTEDVALUE('For slicer'[start])))
var _b = CALCULATE(MAX('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Period]=SELECTEDVALUE('For slicer'[end])))
return _a=_b

Final output:

Best Regards,

Jianbo Li

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

[tomekm]

Just what I needed! Thank you!!

[amitchandak]

@tomekm , Create a new table with distinct periods and add a period rank column in this new table. And join the period of a new table with your table

new column

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

new measures  examples
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s