Hi, I have two tables. One with Inventory of "Units" (and their respective "Projects"), "Price Unit" and "When Date" the "Units" were sold, the other table "DatePrices" shows the dates with different prices for different dates to each of this unit. Im trying to bring the Value each "Unit" should have been priced by looking at the oldest list price {less or equal to} "When Date" it was sold. I have the following DAX in the inventory table trying to bring the Date of the list the column is bringing the value from:
Latest Date = VAR Proy = [Projects] VAR Unit = [Units] VAR DateS = [When Date]
VAR MaxDate =
CALCULATE(
MAX(DatePrices[Date]),
DatePrices[Unit] = Unit,
DatePrices[Project] = Proy,
DatePrices[Date] <= DateS
)
RETURN
MaxDate
and
Should Price = VAR Proy = [Projects] VAR Unit = [Units] VAR DateS = [When Date]
VAR MaxDate =
CALCULATE(
MAX(DatePrices[Date]),
DatePrices[Unit] = Unit,
DatePrices[Project] = Proy,
DatePrices[Date] <= DateS
)
RETURN
CALCULATE(
CALCULATE(
SUM(DatePrices[Price]),
DatePrices[Unit] = Unit,
DatePrices[Project] = Proy,
DatePrices[Date] = Max Date
)
The problem is the value in the columns only works when the value of "When Date" is exactly the same as the DatePrices[Date]. As if the "<=" was "=". I thought it was comparing the date as text when going through the Var DateS and tried DatePrices[Date] <= Date(Year(DateS),Month(DateS),Day(DatesS) but the results are the same. Any ideas what Im missing?
[I tried EVALUATE
//{CALCULATE( MAX(DatePrices[Date]),
FILTER(DatePrices,
DatePrices[Unit] = "1001-2" &&
DatePrices[Project] = "Praia"
&& DatePrices[Date] <= DATE(2023,3,22)
)//)} in Dax Studio and works as it should]
