Finding cost for latest month for a product

[AdityaPowerBI]

Hi All,

I am trying to find latest cost for each product. If we dont have Unit cost for a particular month I want the latest cost for each product. Like we dont have cost for product A for month 202103 hence we need cost for Product A for month 202102

Input:

Sales:

Product	MonthYear	Unit Sales
A	202105	100
A	202104	120
A	202103	130

Cost Data:

Product	MonthYear	UnitCost
A	202105	95
A	202104	100
A	202102	125

Output:

Product	MonthYear	Unit Sales	Unit Cost
A	202105	100	95
A	202104	120	100
A	202103	130	125

[AdityaPowerBI]

@Jihwan_Kim  Thanks for your solution. It is good but actually I am trying to get the cost as a column in Sales table. I want the cost value for each and every sales row

[Jihwan_Kim · Accepted solution]

Hi, @AdityaPowerBI 

Thanks for your feedback, and check the below.

Unit Cost CC =
VAR currentmonthyear = Sales[MonthYear]
VAR maxorcurrentmonthyearincosttable =
MAXX (
FILTER ( Costs, Costs[MonthYear] <= currentmonthyear ),
Costs[MonthYear]
)
RETURN
CALCULATE (
SUM ( Costs[Unit Cost] ),
FILTER ( Costs, Costs[MonthYear] = maxorcurrentmonthyearincosttable )
)

https://www.dropbox.com/s/s7vxjdkmbqlnti2/aditya.pbix?dl=0 

[AdityaPowerBI]

@Jihwan_KimThanks again for the response. When I am implementing your logic, the problem I am facing is maxorcurrentmonthyearincosttable  is fetching me exact same month year as present in sales instead of bringing last available. Is there any kind of messaging or calling medium where I can explain you the issue?

[Jihwan_Kim]

Hi, @AdityaPowerBI 

Thanks for the feedback.

Share your sample pbix file's link here, then I can try to look into it.

[AdityaPowerBI]

Hi @Jihwan_Kim I got it fixed finally. The issue with the current logic was it was not filtering for the product in filter condition in maxorcurrentmonthyearincosttable  variable. I have added a condition of comparing product of sales and cost and it worked. Thanks a lot for timely help.

[Jihwan_Kim]

Hi, @AdityaPowerBI 

Please check the below picture and the sample pbix file's link down below.

It is for creating measures.

Sales Total Measure =

SUMX ( Sales, Sales[Unit Sales] )

Costs Total Measure =

VAR currentmonthyear =

MAX ( MonthYear[MonthYear] )

RETURN

IF (

NOT ISBLANK ( [Sales Total Measure] ),

CALCULATE (

LASTNONBLANKVALUE (

MonthYear[MonthYear],

SUMX (

Costs,

Costs[Unit Cost]

)

),

FILTER (

ALL ( MonthYear ),

MonthYear[MonthYear] <= currentmonthyear

)

)

)

Costs Total Measure fix =

SUMX (

CROSSJOIN (

VALUES ( Products[Product] ),

VALUES ( MonthYear[MonthYear] )

),

[Costs Total Measure]

)

https://www.dropbox.com/s/s7vxjdkmbqlnti2/aditya.pbix?dl=0