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
skanord
Advocate II
Advocate II

Stair shaped matrix help

Hello, 

 

I have been struggling with this problem for a couple of days, and i really need help figuring it out. 

 

I need to make a matrix that looks like this:

InkedMicrosoftTeams-image_LI.jpg

 

 

 

 

 

 

 

 

 

All the blue lines cover sensitive information, but they are essentially just amounts, to do with purchases and sales. 

 

The tricky part is the calculations that need to be in each field. 

The horizontal months indicate the month of purchase and the vertical months (the columns) indicate the month of sale. 

So i.e. field 1 (row: August, column: August) shows the lot numbers that were both purchased and sold in August. Field 2 (row: August, column: September) shows the items bought in august and sold in September. 

 

Essentially the matrix shows how specific products are sold over time. So the calculations should look like this for each field:

 AugustSeptemberOktoberNovemberDecember
Augustpurchase amount (august) - sales amount (august)purchase amount (august) - sales amount (august) - sales amount (september)purchase amount (august) - sales amount (august) - sales amount (september) - sales amount (oktober)purchase amount (august) - sales amount (august) - sales amount (september) - sales amount (oktober) - sales amount (November)purchase amount (august) - sales amount (august) - sales amount (september) - sales amount (oktober) - sales amount (November) - sales amount (december)
September purchase amount (september) - sales amount (september)purchase amount (september) - sales amount (september) - sales amount (oktober)purchase amount (september) - sales amount (september) - sales amount (oktober)  - sales amount (November)purchase amount (september) - sales amount (august) - sales amount (september) - sales amount (oktober) - sales amount (November) - sales amount (december)

 

The important thing is that the sales are tied to the purchase date of the specific lot number. So for field 2 it shouldn't be all lot numbers sold in september, but only the lot numbers bought in august and sold in september. 

 

My data consists of:

 

Purchase table 

Columns: posting date, lot number, purchase amount. 

 

Sales table

Columns: posting date, lot number, sales amount. 

 

I also have a date table. 

 

I hope someone can help me, and the solutions could be using dax or power query or whatever, i just can't alter the source of the data. Please ask questions if something is unclear, i know it is a complicated request, and i did my best to explain it. 

 

Thank you in advance!

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @skanord 

 

Has the problem been solved? If so, you may accept an appropriate post as the solution or post your own solution to help other members find it quickly. Thanks.

 

Best Regards,
Community Support Team _ Jing

Fowmy
Super User
Super User

@skanord 

Please check the attached file. I created a sample dataset as per the details that you shared. I added two dates tables which you can use as filters as well to control the view and filter.

Fowmy_0-1630505148946.png

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you, i will check it out! 

@skanord 

Sure and update your reply.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hello again

 

I finally had time to check out your solution. It helps some of the way, but i think the measure needs to be constructed differently, since it doesn't return the wanted result. I really appreciate your help, and would just like to know how you would tweak it, to get the wanted result. I will try to make an example below, to maybe better explain the result i am looking for:

 

Lets say we have the following information:

 

Purchase table: 

Posting dateLot no     Purchase amount
August 1st15000
August 2nd23000
September 1st      31000

 

Sales amount

Posting dateLot no    Sales amount
August 10th12000
August 20th21500
September 3rd11000
September 20th     12000
September 21st3200
October 1st21500
October  2nd3500
November 5th3300

 

The result should look like this: 

 

The calculation

 AugSepOctNov
Aug8000-3500      8000-3500-3000      8000-3500-3000-1500      
Sep 1000-2001000-200-5001000-200-500-300
Oct    

 

The output

 AugSepOctNov
Aug4500      1500     0 
Sep 800300      0
Oct    

 

So we see that lot no 1 and 2 was purchased in August, which means the total purchase amount for august was 8000, but some of lot no 1 and 2 was also sold in august, therefor we must subtract 3500. 

 

We then see that lot no 3 was purchased in september, this means we go down to the row of september and the column of september and take the purchase amount of lot 3 (1000) and subtract the sales amount of lot 3 in september (1000-200). But lot 1 and 2 was also sold in september, but their amounts should be subtracted in the August row, because they were purchased in august. 

 

Does this make any sense? And how would you change your measure to show this result?

 

Thank you in advance!

@skanord 

I have modified the calculation, please replace the Purchase Measure with he following:

Purchase = 

VAR __LotsPurchased =  VALUES(Purchase[Lot Number])
VAR __MaxSalesDate = MAX('Sales Dates'[Date])
VAR __PurcahseAmount = SUM(Purchase[Purchase Amount])
VAR __SalesAmount = 
    CALCULATE( 
        SUM( Sales[Sales Amount]) , 
        TREATAS( __LotsPurchased,  Sales[Lot Number]),
        'Sales Dates'[Date] <= __MaxSalesDate,
        REMOVEFILTERS('Sales Dates'[Date])   
    )
VAR __SalesDate = 
    CALCULATE( 
        MAX( Sales[ Posting Date]) , 
        TREATAS( __LotsPurchased,  Sales[Lot Number])
    )
RETURN
    IF(
        ISBLANK(__SalesDate), BLANK(),   
        __PurcahseAmount  - __SalesAmount
    )


Based on your example, the purchase and sales amount are the same, that way it's becoming zero at the end in your example, a sort f no-profit scenario. I am eager to know what kind of business scenario this calculation is going to be used. This is ideal for tracking units or quantities. 


 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.