Calculate Sum based on slicer and latest date if slicer value not in rows

[affan]

Hello

I am having two tables Products Table.

Product	Name
FP-001	Finished Product 001
FP-002	Finished Product 002
FP-003	Finished Product 003
FP-004	Finished Product 004

Cost Details Table

Product	Material	Price	Date	Price Model
FP-001	RM-001	4.5	28-05-18	A
FP-001	RM-002	5.3	28-05-18	A
FP-001	RM-003	6.1	28-05-18
FP-001	RM-001	2.4	15-06-18	B
FP-001	RM-002	1.0	15-06-18	B
FP-001	RM-003	5.0	15-06-18	B
FP-001	RM-001	3.3	01-07-18
FP-001	RM-002	5.2	01-07-18
FP-001	RM-003	4.8	01-07-18
FP-002	RM-003	5.7	15-06-18
FP-002	RM-004	2.8	15-06-18	A
FP-002	RM-008	4.8	15-06-18	A

I need a measure to show the total cost of the Material for a Product. The complex part is that I have multiple cost lines for each raw material against a Product. I have to use a filter where the user can select the cost model. So if the user selects Cost Model "A" then the cost of Product "FP-001" should be all three materials cost against "A". But if there is a material which does not have a cost model A then its latest cost price by date should be included.

So in this case 

Product	Material	Price	Date	Price Model
FP-001	RM-001	4.5	28-05-18	A
FP-001	RM-002	5.3	28-05-18	A
FP-001	RM-003	6.1	28-05-18
FP-001	RM-001	2.4	15-06-18	B
FP-001	RM-002	1.0	15-06-18	B
FP-001	RM-003	5.0	15-06-18	B
FP-001	RM-001	3.3	01-07-18
FP-001	RM-002	5.2	01-07-18
FP-001	RM-003	4.8	01-07-18
FP-002	RM-003	5.7	15-06-18
FP-002	RM-004	2.8	15-06-18	A
FP-002	RM-008	4.8	15-06-18	A

Product "FP-001" is having three materials RM-001, RM-002 and RM-003. When I select the slicer for Cost Model "A" I need to add the cost for RM-001 and RM-002 for the cost model, RM-003 does not have a line for this cost model so the line for RM-003 should be with the latest date. 

Please suggest, thanks in anticipation to your contributions.

Regards,

Affan

[v-lili6-msft · Accepted solution]

hi,@affan

     After research, you may try to use this formula like below:

Measure = var mentral=CALCULATETABLE(SUMMARIZE(Cost,Cost[Product],Cost[Material],"date",CALCULATE(MAX(Cost[Date]))),ALLEXCEPT(Cost,Cost[Product]),Cost[Price Model]=BLANK()) return
var _maxdate =CALCULATE(MAXX(mentral,[date]),ALLEXCEPT(Cost,Cost[Product])) return
var _blankvalue=CALCULATE(SUM(Cost[Price]),ALLEXCEPT(Cost,Cost[Product]),Cost[Date]=_maxdate)return

var c=CALCULATE(SUM(Cost[Price]),FILTER(ALLEXCEPT(Cost,Cost[Product]),Cost[Date]=_maxdate&& NOT Cost[Material] IN VALUES(Cost[Material]))) return
CALCULATE(SUM(Cost[Price]))+c

result:

model A

Model B

here is pbix, please try it.

https://www.dropbox.com/s/7li701q8hbu995q/demo.pbix?dl=0

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi,@affan

     After research, you may try to use this formula like below:

Measure = var mentral=CALCULATETABLE(SUMMARIZE(Cost,Cost[Product],Cost[Material],"date",CALCULATE(MAX(Cost[Date]))),ALLEXCEPT(Cost,Cost[Product]),Cost[Price Model]=BLANK()) return
var _maxdate =CALCULATE(MAXX(mentral,[date]),ALLEXCEPT(Cost,Cost[Product])) return
var _blankvalue=CALCULATE(SUM(Cost[Price]),ALLEXCEPT(Cost,Cost[Product]),Cost[Date]=_maxdate)return

var c=CALCULATE(SUM(Cost[Price]),FILTER(ALLEXCEPT(Cost,Cost[Product]),Cost[Date]=_maxdate&& NOT Cost[Material] IN VALUES(Cost[Material]))) return
CALCULATE(SUM(Cost[Price]))+c

result:

model A

Model B

here is pbix, please try it.

https://www.dropbox.com/s/7li701q8hbu995q/demo.pbix?dl=0

Best Regards,

Lin

[affan · Accepted solution]

Hi 

Thank you for the solution. This resolved my issue by taking the maxdate into variable.