cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdityaPowerBI
Helper II
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:

ProductMonthYearUnit Sales
A202105100
A202104120

A

202103

130

 

Cost Data:

ProductMonthYearUnitCost
A20210595
A202104100
A202102

125

 

Output:

ProductMonthYearUnit SalesUnit Cost
A20210510095
A202104120100
A202103

130

125

1 ACCEPTED SOLUTION

Hi, @AdityaPowerBI 

Thanks for your feedback, and check the below.

 

Picture1.png

 

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 

 

 


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

6 REPLIES 6
AdityaPowerBI
Helper II
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

Hi, @AdityaPowerBI 

Thanks for your feedback, and check the below.

 

Picture1.png

 

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 

 

 


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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

Hi, @AdityaPowerBI 

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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
Community Champion
Community Champion

Hi, @AdityaPowerBI 

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

It is for creating measures.

 

Picture1.png

 

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors