Compare customer change between two dates from account table

Hello everyone! I am new to Power BI, like it very much so far, but now I am struggling with data aggegation and comparison. Example: I have an account table that also includes some inform...

Need Help

v-yueyunzh-msft

3 years ago

Hi , ChrisCBR

For the column , we need the static fields. Due to we can not put the measure on the headers . So we need to create two dimention table.

Here are the steps you can refer to :

(1)We need to click "New Table" to create two tables:

Row = VALUES('Table'[RatingGrade])

Column = VALUES('Table'[RatingGrade])

(2)Then we need to create two measures:

Expouser 1-1 = var _row =SELECTEDVALUE('Row'[RatingGrade])
var _column = SELECTEDVALUE('Column'[RatingGrade])
var _date1 = SELECTEDVALUE('Date1'[Date1])
var _date2 = SELECTEDVALUE('Date2'[Date2])
var _row_t  =  FILTER( 'Table' , 'Table'[Date] =_date1 && 'Table'[Defaulted]=1)
var _column_t = FILTER('Table','Table'[Date] =_date2 && 'Table'[Defaulted]=0)
var _row_customerNumber = MAXX(FILTER(_row_t , [RatingGrade] = _row),[CustomerNumber])
var _column_customerNumber = MAXX(FILTER(_column_t , [RatingGrade] = _column),[CustomerNumber])
var _in_customnumber =INTERSECT( SELECTCOLUMNS(_row_t , "CustomerNumber",[CustomerNumber])  ,  SELECTCOLUMNS(_column_t , "CustomerNumber",[CustomerNumber]))
var _row_value =DISTINCT(SELECTCOLUMNS( FILTER(_row_t , [CustomerNumber] in  _in_customnumber ) , "RatingGrade",[RatingGrade]))
var _column_value = DISTINCT(SELECTCOLUMNS( FILTER(_column_t , [CustomerNumber] in  _in_customnumber ) , "RatingGrade",[RatingGrade]))
var _value =SUMX( FILTER( _row_t,[RatingGrade] = _row) , [Exposure])
return
IF(_row in _row_value && _column in _column_value && _row_customerNumber=_column_customerNumber,   _value  ,           BLANK())

Expouser 1-2 = var _row =SELECTEDVALUE('Row'[RatingGrade])
var _column = SELECTEDVALUE('Column'[RatingGrade])
var _date1 = SELECTEDVALUE('Date1'[Date1])
var _date2 = SELECTEDVALUE('Date2'[Date2])
var _row_t  =  FILTER( 'Table' , 'Table'[Date] =_date1 && 'Table'[Defaulted]=1)
var _column_t = FILTER('Table','Table'[Date] =_date2 && 'Table'[Defaulted]=0)
var _row_customerNumber = MAXX(FILTER(_row_t , [RatingGrade] = _row),[CustomerNumber])
var _column_customerNumber = MAXX(FILTER(_column_t , [RatingGrade] = _column),[CustomerNumber])
var _in_customnumber =INTERSECT( SELECTCOLUMNS(_row_t , "CustomerNumber",[CustomerNumber])  ,  SELECTCOLUMNS(_column_t , "CustomerNumber",[CustomerNumber]))
var _row_value =DISTINCT(SELECTCOLUMNS( FILTER(_row_t , [CustomerNumber] in  _in_customnumber ) , "RatingGrade",[RatingGrade]))
var _column_value = DISTINCT(SELECTCOLUMNS( FILTER(_column_t , [CustomerNumber] in  _in_customnumber ) , "RatingGrade",[RatingGrade]))
var _value =SUMX( FILTER( _column_t,[RatingGrade] = _column) , [Exposure])
return
IF(_row in _row_value && _column in _column_value && _row_customerNumber=_column_customerNumber,   _value  ,           BLANK())

(3)Then we put the fields we need on the visual and we will meet your need: