Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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
Super User
Super User

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.