Stair shaped matrix help

Question

Hello,&nbsp;&nbsp;I have been struggling with this problem for a couple of days, and i really need help figuring it out.&nbsp;&nbsp;I need to make a matrix that looks like this:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;All the blue lines cover sensitive information, but they are essentially just amounts, to do with purchases and sales.&nbsp;&nbsp;The tricky part is the calculations that need to be in each field.&nbsp;The horizontal months indicate the month of purchase and the vertical months (the columns) indicate the month of sale.&nbsp;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.&nbsp;&nbsp;Essentially the matrix shows how specific products are sold over time. So the calculations should look like this for each field:&nbsp;AugustSeptemberOktoberNovemberDecemberAugustpurchase 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&nbsp;purchase amount (september) - sales amount (september)purchase amount (september) - sales amount (september) - sales amount (oktober)purchase amount (september) - sales amount (september) - sales amount (oktober)&nbsp; - sales amount (November)purchase amount (september) - sales amount (august) - sales amount (september) - sales amount (oktober) - sales amount (November) - sales amount (december)&nbsp;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.&nbsp;&nbsp;My data consists of:&nbsp;Purchase table&nbsp;Columns: posting date, lot number, purchase amount.&nbsp;&nbsp;Sales tableColumns: posting date, lot number, sales amount.&nbsp;&nbsp;I also have a date table.&nbsp;&nbsp;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.&nbsp;&nbsp;Thank you in advance!

anonymous · Answer

Thank you, i will check it out!&nbsp;

fowmy · Answer

Anonymous&nbsp;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.

&nbsp;
&nbsp;
&nbsp;

fowmy · Answer

Anonymous&nbsp;Sure and update your reply.

anonymous · Answer

Hello again&nbsp;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:&nbsp;Lets say we have the following information:&nbsp;Purchase table:&nbsp;Posting dateLot no&nbsp; &nbsp; &nbsp;Purchase amountAugust 1st15000August 2nd23000September 1st&nbsp; &nbsp; &nbsp;&nbsp;31000&nbsp;Sales amountPosting dateLot no&nbsp; &nbsp;&nbsp;Sales amountAugust 10th12000August 20th21500September 3rd11000September 20th&nbsp; &nbsp; &nbsp;12000September 21st3200October 1st21500October&nbsp; 2nd3500November 5th3300&nbsp;The result should look like this:&nbsp;&nbsp;The calculation&nbsp;AugSepOctNovAug8000-3500&nbsp; &nbsp; &nbsp;&nbsp;8000-3500-3000&nbsp; &nbsp; &nbsp;&nbsp;8000-3500-3000-1500&nbsp; &nbsp; &nbsp;&nbsp;Sep&nbsp;1000-2001000-200-5001000-200-500-300Oct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The output&nbsp;AugSepOctNovAug4500&nbsp; &nbsp; &nbsp;&nbsp;1500&nbsp; &nbsp; &nbsp;0&nbsp;Sep&nbsp;800300&nbsp; &nbsp; &nbsp;&nbsp;0Oct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;Does this make any sense? And how would you change your measure to show this result?&nbsp;Thank you in advance!

fowmy · Answer

Anonymous&nbsp;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] &lt;= __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.&nbsp;
&nbsp;

Forum Discussion

Stair shaped matrix help

6 Replies

Related content

Shape map

Shape Map

insert custom shape

Matrix table

Working with Shapes

	August	September	Oktober	November	December
August	purchase 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)