Measure to calculate based off of date range

[NBOnecall]

Hi,

I have 3 tables and would like to calculate a new mesure called "MIRExtCost".

So my first table is Item. It tells me the item number and what manufacturer it is.

The second table is ItemCost. It says what day it sold and what the cost of the item was on that date.

The last table is the MIR. This list a Start and End date of a discount on the cost.

I would like to be able to have a measure that would calculate a new cost for the ItemCost table. If the sales date falls between the StartDate and EndDate of the MIR table then, the PercentDiscount is subtracted from the Cost field in the ItemCost table.

For example the measure for the sale on 1/5/2021 for item 1 would be = 25-(25*.05) or $23.75. But if it the sale was on 1/1/2021 like the first row, it would stay at the $20 cost.

Thanks,

Noel

[NBOnecall]

@Ashish_Mathurand @V-lianl-msft I have doubled checked and and making sure there was a realtionship between the Item table and the MIR table. There is, but what I was able to research while in doing so is that The MIRExtCost is not summing accrutley and I think this is the issue.

For example I have 3 sales of a single product across a few months. Each one with a MIRExtCost as $23.38 but in a matrix visual the measure only gets summed to $23.38 vs. $70.14. I have had this issue before with Measures, but I don't recall if I was able to get around it. I know measures can't always sum within a table.

Is it best to create a calculated column in the ItemCost table that does what the measure is trying to do?

[Ashish_Mathur]

Hi,

Write a calculated column formula in Table 2 to bring over Manufacturer from Table 1.  Now write another calculated column formula in Table 2 which will search for Date and Manufacturer in Table 3 and bring over the Percent Discount from Table 3.  Then write a simple SUM measure giving the Percent Discount column of Table 2 as an input.

Hope this helps. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[NBOnecall]

I have not had any luck working out a way to match manufacturer ID of the MIR table to the sale of the item. Any thoughts?

[V-lianl-msft]

What is the relationship between these tables? Maybe that's the problem

I created the following relationship based on the sample data you provided.

Item 1->* ItemCost
Item 1->*MIR

If the problem still exists, please remove sensitive data and share your pbix.

[V-lianl-msft]

Hi @NBOnecall ,

Please create a measure like below:

Measure = 
var s_date = MAX(MIR[StartDate])
var e_date = MAX(MIR[EndDate])
var new_cost =
 CALCULATE(
     SUM(ItemCost[Cost])*(1-MAX(MIR[PercentDiscount])),
     FILTER(ItemCost,
     ItemCost[Sales Date]>=s_date&&ItemCost[Sales Date]<=e_date)
     )
return IF(ISBLANK(new_cost),MAX(ItemCost[Cost]),new_cost)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NBOnecall]

This is helpful, but I still need to match up manufacturers. So on the Item Table it has a manufacturer ID and ItemID. On the ItemCost table it has the the itemID. I need to say from the MIR Table if that Manufacturer is the item that sold then the discount is applied.

Thanks!

[NBOnecall]

I still haven't had any luck with the measure.

[amitchandak]

@NBOnecall , Try a mesure like , use with ItemCost[item] ,ItemCost[sales date]

measure =
var _max = calculate(max(MIR[percent Discount]), filter(ItemCost, ItemCost[sales date] <=max(MIR[end Date]) && ItemCost[sales date] >=Min(MIR[Start Date])))

[NBOnecall]

This doesn't appear to take into account link what manufactruer rebate goes with what item sale. The Item table links the manufacturer, and the ItemCost table only has the item number.