cancel
Showing results for
Did you mean:
Helper II

## Finding cost for latest month for a product

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
1 ACCEPTED SOLUTION
Community Champion

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 )
)

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

6 REPLIES 6
Helper II

@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

Community Champion

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 )
)

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Helper II

@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?

Community Champion

Thanks for the feedback.

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

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Helper II

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.

Community Champion

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]
)

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.