Forum Discussion

tomekm's avatar
tomekm
Helper III
3 years ago
Solved

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

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:

 

IDAttributePeriod
1AAAP01
2BBBP01
3CCCP01
5AAAP02
2BBBP02
3CCCP02
5AAAP03
67BBBP03
9CCCP03

 

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.

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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's avatar
      tomekm
      Helper III
      3 years ago

      Just what I needed! Thank you!!