Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MCstudio
Helper I
Helper I

Portfolio vintage analysis matrix in Power BI

Hi people)

Need a help in BI.

I am going to do a portfolio vintage analysis.

For example, I have got an excel input file with such a structure:

Consideration date

Name

Receiving date

Amount of Agreement

(in origin will be a number)

Amount

(in origin will be a number)

March 19

Tome

February 19

P1

M11

March 19

Bob

March 19

P2

M21

March 19

Allen

March 19

P3

M31

March 19

Mike

March 19

P4

M41

April 19

Tome

February 19

P1

M12

April 19

Bob

March 19

P2

M22

April 19

Mike

March 19

P4

M42

April 19

Jim

April 19

P5

M51

April 19

Nike

April 19

P6

M61

May 19

Tome

February 19

P1

M13

May 19

Bob

March 19

P2

M23

May 19

Mike

March 19

P4

M43

May 19

Jim

April 19

P5

M52

May 19

Nike

April 19

P6

M62

May 19

Smith

May 19

P7

M71

 

I need to have such a BI matrix with calculation:

 

Consideration date

Receiving date

March 19

April 19

May 19

February 19

=M11/P1

=M12/P1

=M13/P1

March 19

=(M21+M31+M41)/(P2+P3+P4)

=(M22+M42)/(P2+P3+P4)

=(M23+M43)/(P2+P3+P4)

April 19

-

=(M51+M61)/(P5+P6)

=(M52+M62)/(P5+P6)

May 19

-

-

=M71/P7

 

I made something like that, but denominators in the matrix were changing by column to column, but it must by constant.

 

How can it be done in BI?)

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Given the data (as seen in the MS Excel screenshot below), this is the result i got (See second screenshot).  This is the measure i wrote

Measure = SUM(Data[Amount])/SUM(Data[Amount of Agreement])

Untitled.pngUntitled1.png 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

Our denominator must be constant. As us see, Allen received amount in March 19, but he didn't have it in April 19 and so on. That's why your denominator decrease:

Capture3.PNG

We are doing portfolio analysis, not by client.

The target solution in Power BI, which I can make by excel, is this:

Capture2.PNG

 

 

Hi,

I am not sure of how robut this solution is but try it out.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Dear @Ashish_Mathur 
Yes it works, but it is a bulky method 😕
I think, there can be a easier one.
Even if we will have such a table structure for denominator, it will work:

Capture.PNG

This limitation will help: denominator we need is MAX for Receiving date, and it is first coming Consideration date.

Do it Yourself now.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I can do that in Excel, I am not so familiar with DAX.
In Excel I will count Max of each rows:

Capture.PNG

divide each column of numerator to denominator MAX column:

Capture2.PNG

and will have the target solution:

Capture3.PNG

v-xicai
Community Support
Community Support

Hi @MCstudio ,

 

You can create column in DAX below.

 

Column1 = DIVIDE(CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date])&&Table1[Consideration date]=EARLIER(Table1[Consideration date]))), CALCULATE(SUM(Table1[Amount of Agreement]),FILTER(Table1,Table1[Consideration date]=EARLIER(Table1[Consideration date]))))

 

Best Regards,

Amy

 

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

 

Hi @v-xicai ,

I have checked your DAX formula and numerator -  denominator  separately:

  • numerator of your formula not suit, it must be: CALCULATE(SUM(Table1[Amount]))
  • denominator is not suit too: I have made some changes: instead of Consideration date I put Receiving date. But only benefit that I reached was that formula suitably works only for first and last Receiving date months rows.
     
    Column1 = DIVIDE(CALCULATE(SUM(Table1[Amount])), CALCULATE(SUM(Table1[Amount of Agreement]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date]))))
     
    I think, something must be changed in denominator 😕

Hi @MCstudio ,

 

What about DAX below? The EARLIER function here is used to categorize the date column.

 

Column1 = DIVIDE(CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date])&&Table1[Consideration date]=EARLIER(Table1[Consideration date]))), CALCULATE(SUM(Table1[Amount of Agreement]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date]))))

 

Best Regards,

Amy

Dear @v-xicai 

as I said, something is wrong with denominator.
If we use this numbers:

Consideration dateNameReceiving dateAmount of AgreementAmount
Mar-19TomeFeb-19111
Mar-19BobMar-19221
Mar-19AllenMar-19331
Mar-19MikeMar-19441
Apr-19TomeFeb-19112
Apr-19BobMar-19222
Apr-19MikeMar-19442
Apr-19JimApr-19551
Apr-19NikeApr-19661
May-19TomeFeb-19113
May-19BobMar-19223
May-19MikeMar-19443
May-19JimApr-19552
May-19NikeApr-19662
May-19SmithMay-19771

 

With your formula, we will have:

Capture.PNG

But our target result is:

Capture2.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.