Forum Discussion

Helper I

3 years ago

Solved

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:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

1119Exposure.pbix70 KB

v-yueyunzh-msft

Community Support

3 years ago

Hi , ChrisCBR

Thank you for your sql code to me , now i can fully understand your requirement and here is my solution based on it:

(1)My test data is the same as you provided.

(2)We need to create two Date table to use as a slicer. We need to click "New Table" to create two tables:

Date1 = VALUES('Table'[Date])

Date2 = VALUES('Table'[Date])

(3)We need to create two measures to calculate your exposures:

Exposure1 = var _date1 = MAX('Date1'[Date1])
var _date2 = MAX('Date2'[Date2])
var _t1 = FILTER('Table' , 'Table'[Date] = _date1 && 'Table'[Defaulted] =1 )
var _t1_CustomerNumber = SELECTCOLUMNS(_t1,"CustomerNumber" , [CustomerNumber])
var _t2 = FILTER('Table' , 'Table'[Date]=_date2 && 'Table'[Defaulted]=0)
var _t2_CutomerNumber = SELECTCOLUMNS(_t2 , "CustomerNumber",[CustomerNumber])
var _Innerjoin_number =DISTINCT( INTERSECT(_t1_CustomerNumber , _t2_CutomerNumber))
var _t_exposure1 =FILTER(_t1, [CustomerNumber] in _Innerjoin_number)
return
SUMX(_t_exposure1 , [Exposure])

Exposure2 = var _date1 = MAX('Date1'[Date1])
var _date2 = MAX('Date2'[Date2])
var _t1 = FILTER('Table' , 'Table'[Date] = _date1 && 'Table'[Defaulted] =1 )
var _t1_CustomerNumber = SELECTCOLUMNS(_t1,"CustomerNumber" , [CustomerNumber])
var _t2 = FILTER('Table' , 'Table'[Date]=_date2 && 'Table'[Defaulted]=0)
var _t2_CutomerNumber = SELECTCOLUMNS(_t2 , "CustomerNumber",[CustomerNumber])
var _Innerjoin_number =DISTINCT( INTERSECT(_t1_CustomerNumber , _t2_CutomerNumber))
var _t_exposure2 =FILTER(_t2, [CustomerNumber] in _Innerjoin_number)
return
SUMX(_t_exposure2 , [Exposure])

(4)Then we can put the 'Date1' and 'Date2' on the two slicer and the two measures on the card visual and we will meet your need :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

1119Exposure.pbix64 KB

ChrisCBR

Helper I

3 years ago

Thank you very much!

This way all four use cases for my first problem are solved. I have one more problem with my data. I am still not sure how to deal with my categories.

Going back to my original post:

However, I also need this information in different visualisations, for instance I want to see the current exposure of all newly defaulted customers as a Card or visualize the new defaults by individual default classes. Without the Customer Nr in my visualisation, my filters do not work.

Is there a way to expand this example so I can use for example the rating grade from my example at date 1 on the x-axis (columns), the rating grade at date 2 on the y-axis (rows) and our newly calculated exposure 1 and exposure 2 as the values of a matrix?

Thank you again and best regards

Chris

v-yueyunzh-msft

Community Support

3 years ago

Hi , ChrisCBR

I do not fully understand the "visualize the new defaults by individual default classes.".

Can you tell me what the filed you want to put on the visual and the end result on the visual , such as give a end table you want based on the test data .

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

ChrisCBR

Helper I

3 years ago

Hello!

here is a visualisation, based on my second example:

2) default between 30.09.2022 and 31.03.2022

Rating Migration of Exposure 1
*Ratinggrade 2*	C	D
Ratinggrade1	Exposure1	Exposure1
FFF	9.400.000	-
FF	-	48.000.000

SQL-code for getting the data:

SELECT RatingGrade1

, RatingGrade2

, sum(Exposure1) Exposure1

,sum(menge.Exposure2) Exposure2

from (

SELECT a.*,b.Exposure2, b.RatingGrade2 FROM (

SELECT CustomerNumber

,Defaulted

,RatingGrade as RatingGrade1

,sum(Exposure) Exposure1

from cbr.BeispielDefaultBI

where Defaulted=1

and Date='20220930'

GROUP BY CustomerNumber

,Defaulted

,RatingGrade

INNER JOIN (

SELECT CustomerNumber

,Defaulted

,RatingGrade as RatingGrade2

,sum(Exposure) Exposure2

from cbr.BeispielDefaultBI

where Defaulted=0

and Date='20220331'

GROUP BY CustomerNumber

,Defaulted

,RatingGrade

)b on b.CustomerNumber = a.CustomerNumber

)menge

GROUP BY RatingGrade1

, RatingGrade2

Best regards

Chris

v-yueyunzh-msft

Community Support

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:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

1119Exposure.pbix70 KB

Forum Discussion

Compare customer change between two dates from account table

Related content

Table Group , Custom comparer

Customer Scorecard

Comparing Customers' Purchased Products

Custom Visual

Table Sort , sort order , comparer