Costing Problem - LookupValue Use?

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I have a problem I hope you may help me out, and I strongly believe this could be a good example for other users.

I’ve attached my dummy file for reference.
Dummy File

This situation comes from a real problem I am experiencing in my real job. In our case, product-costs are updated in a quarterly basis, so this means to calculate the real-margin you cannot use “sales_price” - “related_cost” from item_master table.
The point is that “Product_Cost” changes with the time, so you need to define some measures to calculate dinamically the cost for the product for every moment.

Question

The first problem I have to face, is related with the text-format used on dates. Financial Dept provides a table with year-quarter, with text-type as format. Please see above.

Second problem, is how relate the cost for every sale. Sales_Table contains “invoice_date” which is a complete date, fully linkeable with Dates_Table, but it doesn’t contains a “YearQuarter” column.

Could you help me with this problem?, could you give some tips-advices to move forward?

Thanks Sam.

2

Hi Sam:

I didn’t say, but the goal is to find a way to get the profit for every sale, this means “Net Price” (Sales_Table) - “Product_Costs” (FCosting Table).

I guess LookupValue may help on this case, but I don’t see a easy way to continue here.

Thank you for your time.

3

Ok I’ve looked at it. Yes, this is a good example. Likely a few ways to ultimately solve this.

Really need to work on simplifying your model. This is very difficult to understand

image
image860×753 28.6 KB

Please go through this course if you haven’t already.

Due to a bit going on here, these are the steps I would take.

First I would do this

image

This in the sales fact table I would get a quarter in there like so

image
image1315×295 28.4 KB

Then to get the dynamic product costs into the sales table I would use a formula like so

image
image1414×377 32.3 KB 

Current Cost = 
MINX(
    FILTER( SUMMARIZE( fCosting, fCosting[Item_Number], fCosting[Year], fCosting[Quarter], fCosting[Product Costs]),
        Sales_fact[Item_Number] = fCosting[Item_Number] &&
        Sales_fact[Invoice_Year] = fCosting[Year] &&
        Sales_fact[Current Quarter] = fCosting[Quarter] ),
            fCosting[Product Costs] )

Now that you have the costs inside this table, then you can use simple iterating functions like SUMX to get what you need.

To me, this is probably the simplest way to complete this

You could also probably complete all this in measures, which may be more optimal.

I work on that and place these sort of scenarios on the list for future demos.

Chrs
Sam

4

Hi Sam:

Your solutions looks brillant.

I have created a new one, following the thread’ subject, this is, using LookUpValue function instead. It works either…

Costing = LOOKUPVALUE(Costing_Fact[Product Costs];Costing_Fact[Item_Number];Sales_fact[Item_Number];Costing_Fact[YearQuarter];Sales_fact[YearQuarter])

I hope it helps other users in their developments. Link to the file as usual :hugs:

Dummy File

Nice work for both of us :-):sunglasses: … Keep waiting for your feedback.

Kind regards.

5

Yep nice one. Looks good