Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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) |
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!
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
@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.
⭕ 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.
⭕ 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 date | Lot no | Purchase amount |
August 1st | 1 | 5000 |
August 2nd | 2 | 3000 |
September 1st | 3 | 1000 |
Sales amount
Posting date | Lot no | Sales amount |
August 10th | 1 | 2000 |
August 20th | 2 | 1500 |
September 3rd | 1 | 1000 |
September 20th | 1 | 2000 |
September 21st | 3 | 200 |
October 1st | 2 | 1500 |
October 2nd | 3 | 500 |
November 5th | 3 | 300 |
The result should look like this:
The calculation
Aug | Sep | Oct | Nov | |
Aug | 8000-3500 | 8000-3500-3000 | 8000-3500-3000-1500 | |
Sep | 1000-200 | 1000-200-500 | 1000-200-500-300 | |
Oct |
The output
Aug | Sep | Oct | Nov | |
Aug | 4500 | 1500 | 0 | |
Sep | 800 | 300 | 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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |