Track Cost Changes

[diegoamferreira]

I'm trying to track the cost of products using a table that shows me only the price update.

I have the fact table that shows me the sales of products and the cost table with the changes in cost per product...

So each time a price changes, a new record will go into the table with corresponding SKU, and date of the change.

I need a measure to bring the cost on the day of sale.

I tried using that expression, but I had a lot of performance problems as you can see

Product Cost = CALCULATE (
                    SUM(d_Cost[cost]),
                    FILTER (d_Cost,
                        d_Cost[DATE]
                            = CALCULATE (
                                LASTNONBLANK ( d_Cost[DATE], 1 ),
                                FILTER (
                                    d_Cost,
                                    d_Cost[DATE] <= LASTDATE(f_Sales[Date])))))

Table fSales

Spoiler

Date	Total	SKU
18/05/2020	R$ 236,99	1224
08/04/2020	R$ 206,11	1787
08/05/2020	R$ 197,9	1787
20/05/2020	R$ 225,8	1787
19/05/2020	R$ 394,99	2061
21/05/2020	R$ 394,99	2061
20/04/2020	R$ 71,76	2768
04/05/2020	R$ 379,9	2786
15/05/2020	R$ 324,99	2786
18/05/2020	R$ 349,99	2786
18/05/2020	R$ 324,99	2786
18/05/2020	R$ 349,99	2786
19/05/2020	R$ 324,99	2786
19/05/2020	R$ 324,99	2786
20/05/2020	R$ 324,99	2786
22/05/2020	R$ 332,89	2786
07/04/2020	R$ 450,15	2792
02/05/2020	R$ 440,25	2792
14/05/2020	R$ 440,25	2792
19/05/2020	R$ 132,89	2986
21/05/2020	R$ 120,79	2986
22/05/2020	R$ 121,99	2986
24/05/2020	R$ 128,89	2986
17/04/2020	R$ 157,85	3009
07/05/2020	R$ 150,95	3009
16/05/2020	R$ 150,95	3009
16/05/2020	R$ 326,79	3124
16/05/2020	R$ 114,79	3264
17/05/2020	R$ 116,8	3264
18/05/2020	R$ 120,79	3264
18/05/2020	R$ 127,79	3264
19/05/2020	R$ 114,79	3264
20/05/2020	R$ 126,79	3264
21/05/2020	R$ 116,79	3264
21/05/2020	R$ 104,8	3264
23/05/2020	R$ 104,8	3264
22/04/2020	R$ 579,4	3286
16/03/2020	R$ 280,14	3426
13/04/2020	R$ 280,15	3426
07/05/2020	R$ 269,99	3426
07/05/2020	R$ 269,99	3426

Table dCost

Spoiler

SKU	PROVIDER	DATE	DESCRIPTION	COST
6407	BRAND 3	01/01/2020	PRODUCT 1	101,05
6465	BRAND 2	01/01/2020	PRODUCT 2	49,96
3426	BRAND 1	01/01/2020	PRODUCT 3	86,4
2786	BRAND 3	01/01/2020	PRODUCT 4	171,5
5757	BRAND 3	01/01/2020	PRODUCT 5	37,2
6085	BRAND 1	01/01/2020	PRODUCT 6	88,65
1787	BRAND 1	01/01/2020	PRODUCT 7	98,95
6501	BRAND 1	01/01/2020	PRODUCT 8	77,81
2986	BRAND 3	01/01/2020	PRODUCT 9	43,92
3858	BRAND 3	01/01/2020	PRODUCT 10	48
6195	BRAND 2	01/01/2020	PRODUCT 11	22,54
5991	BRAND 1	01/01/2020	PRODUCT 12	34,8
5619	BRAND 3	01/01/2020	PRODUCT 13	173,86
2061	BRAND 1	01/01/2020	PRODUCT 14	118,5
3264	BRAND 3	01/01/2020	PRODUCT 15	44,06
6431	BRAND 2	01/01/2020	PRODUCT 16	429,84
5469	BRAND 3	01/01/2020	PRODUCT 17	128,72
6255	BRAND 1	01/01/2020	PRODUCT 18	19,07
5175	BRAND 3	01/01/2020	PRODUCT 19	123,8
6099	BRAND 1	01/01/2020	PRODUCT 20	219,49
3234	BRAND 3	01/01/2020	PRODUCT 21	224,36
5435	BRAND 2	01/01/2020	PRODUCT 22	149,8
5731	BRAND 2	01/01/2020	PRODUCT 23	398,7
5363	BRAND 2	01/01/2020	PRODUCT 24	73,06
5707	BRAND 2	01/01/2020	PRODUCT 25	43,2
4443	BRAND 1	01/01/2020	PRODUCT 26	52,8
3286	BRAND 3	01/01/2020	PRODUCT 27	266,53
6017	BRAND 1	01/01/2020	PRODUCT 28	117,66
3132	BRAND 1	01/01/2020	PRODUCT 29	146
2792	BRAND 1	01/01/2020	PRODUCT 30	105,66
6065	BRAND 2	01/01/2020	PRODUCT 31	101,75
6261	BRAND 2	01/01/2020	PRODUCT 32	36,29
3009	BRAND 2	01/01/2020	PRODUCT 33	70,95
3582	BRAND 3	01/01/2020	PRODUCT 34	11,07
1224	BRAND 2	01/01/2020	PRODUCT 35	118,5
6489	BRAND 2	01/01/2020	PRODUCT 36	216,72
4487	BRAND 2	01/01/2020	PRODUCT 37	58,46
5981	BRAND 1	01/01/2020	PRODUCT 38	95,97
6131	BRAND 1	01/01/2020	PRODUCT 39	10,66
6521	BRAND 3	01/01/2020	PRODUCT 40	144,41
6001	BRAND 2	01/01/2020	PRODUCT 41	186,17
3262	BRAND 2	01/01/2020	PRODUCT 42	24,71
5729	BRAND 3	01/01/2020	PRODUCT 43	321,86
6185	BRAND 3	01/01/2020	PRODUCT 44	24,18
2768	BRAND 3	01/01/2020	PRODUCT 45	17,94
6483	BRAND 2	01/01/2020	PRODUCT 46	40,89
3124	BRAND 1	01/01/2020	PRODUCT 47	118,36
3568	BRAND 3	01/01/2020	PRODUCT 48	13,62
6473	BRAND 1	01/01/2020	PRODUCT 49	98,67
6067	BRAND 3	01/01/2020	PRODUCT 50	131,06
4323	BRAND 1	01/01/2020	PRODUCT 52	36,99
6457	BRAND 1	01/01/2020	PRODUCT 53	58,38
3380	BRAND 2	01/01/2020	PRODUCT 54	8,12
3426	BRAND 1	20/04/2020	PRODUCT 3	100
3426	BRAND 1	20/05/2020	PRODUCT 3	90
6195	BRAND 2	17/02/2020	PRODUCT 11	25
6195	BRAND 2	21/02/2020	PRODUCT 11	7

 the 'current' price is the 'last price' recorded

Any help would be greatly appreciated.

Many thanks.

[v-alq-msft]

Hi, @diegoamferreira 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @diegoamferreira 

You may try the following measure.

ResultCost = 
var _maxdate = 
CALCULATE(
    MAX(dCost[DATE]),
    FILTER(
        ALL(dCost),
        dCost[SKU] = SELECTEDVALUE(fSales[SKU])
    )
)
return
MAXX(
    FILTER(
        ALL(dCost),
        dCost[SKU] = SELECTEDVALUE(fSales[SKU])&&
        dCost[DATE] = _maxdate
    ),
    dCost[ COST ]
)

Result:

Best Regards

Allan

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

[diegoamferreira]

Thank you!

[AlB]

Hi @diegoamferreira 

Hi, can you try to explain based on  an example

1.what the measure does,

2. what the expected result is for that example and

3. the steps in your measure code?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers