Last record before slicer

[GuiBrum]

I have a table with production calculating the cost of each production batch.

Another table showing the sales price of said products.

The visualization should show the average sales price, the average cost and calculate the profit.

When I set the visualization slicer to show 2020 for example, if I sold the material this year but produced last year, the visualization will not find the cost and show 100% profit.

table example below:

Production Table
Batch	Date	Product	Cost
1	Oct-19	A	$       0.83
2	Oct-19	B	$       0.88
3	Nov-19	A	$       0.81
4	Nov-19	B	$       0.86
5	Dec-19	A	$       0.82
6	Jan-20	B	$       0.85
7	Feb-20	B	$       0.89
8	Mar-20	B	$       0.83
9	Apr-20	B	$       0.83
10	May-20	B	$       0.90
11	Jun-20	B	$       0.84

Sales Table
Date	Product	Price	the slicer does not find cost for product A	Margin
Dec-19	A	$       1.18	$       0.82	$       0.36
Jan-20	A	$       1.20	$           -	$       1.20
Jan-20	B	$       1.19	$       0.85	$       0.34
Feb-20	B	$       1.19	$       0.89	$       0.30
Mar-20	B	$       1.19	$       0.83	$       0.36
Apr-20	B	$       1.19	$       0.83	$       0.36
Apr-20	A	$       1.20	$           -	$       1.20
May-20	B	$       1.19	$       0.90	$       0.29
Jun-20	B	$       1.19	$       0.84	$       0.35

Thank you for any help in this direction

[V-pazhen-msft]

@GuiBrum 

I see the problem. However, I am confused with your example tables, what i can think of is you may relate the tables by related the batches columns, and then create a slicer with Date column. But I cannot see the relationship and the calendar of your model.

Please provide a sample pbix including the relationship and calendar, so we can test and find a solution for you.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@GuiBrum , I am not able to figure out the logic you have for price in a month . But I would have created a calculated column to get the price in the sales table. preferably use date in place on the month

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) 
Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date]) 

You can add remove condition as per need

[GuiBrum]

The month and year on the table are really examples, the actual information is Date.

The problem is that the date slicer is handling the sales date correctly, but it is not picking up the production cost if the material was made before the slicer cut off.

[GuiBrum]

Hello, the product price is sales price (for each customer) so it is not calculated.

the batch cost is calculated within Power Query, the tables are available from power query (it has much more than only the columns I show - these seem to be the relevant columns).

I tried to be concise on the question. Here is my model