Forum Discussion
Measure doesn't display all cell value
amitchandak thank you for your answer. I deleted the commented part in the formula, I totally forgot about that.
This formula: YEAR ( 'F - Inventory'[Physical date] ) = 1900 || 'F - Inventory'[Physical date] > data
means that I want to filter by the year 1900 that is the default year of items which transaction is not yer arrived in the warehouse (otherwise it has a plausible date) or by a date that is after my relative date values in the column (so the item is arrived in a date after). This because I have to reconstruct the portfolio in the past, so every row, that is referred to a particular day has to calculate the value based on that day...
The condition are those I explained: for each day I want to see value of the orders purchased but not arrived in my warehouse yet . What I want is to have values on all the cell of the column, instead I have some day valorized and some not (see the link to image in my last post).
Anonymous Can you post sample data and expected output? Thanks.
I've cut out the other column and now all cells are populated, but that is not the result I expected because there are very big variation from one cell to another. What I expect is a table like this (second column values are what I want):
DECLARE @CompanyId INT = 03
DECLARE @Year INT = 2020SELECT CONVERT(DATE, DimDate.DtDateTime) AS DATE
, SUM(CASE WHEN (FactInventoryTrans.UnitCost is null) THEN FactPurchaseOrders.GrossPriceMst ELSE FactInventoryTrans.UnitCost END *FactInventoryTrans.Qty) AS Cost
, SUM(FactInventoryTrans.Qty) AS qty
FROM DimDate
left join FactInventoryTrans ON DimDate.DtDateTime>=FactInventoryTrans.CreatedDateTime
inner join FactPurchaseOrders ON FactInventoryTrans.InventTransId=FactPurchaseOrders.InventTransId
join DimItem ON FactInventoryTrans.FK_DimItemKey=DimItem.DimItemKey
WHERE YEAR(DimDate.DtDateTime) = @Year
and (YEAR(FactInventoryTrans.PhysicalDate)=1900 or FactInventoryTrans.PhysicalDate>DimDate.DtDateTime)
and DimItem.ItemType='item'
and FactInventoryTrans.CompanyId = @CompanyId
GROUP BY DimDate.DtDateTime
ORDER BY DimDate.DtDateTimeHere's the file where I try the formula: https://drive.google.com/file/d/1yU4ljV7Q5V6-8VnGAqmc8CxyJ6wjVvje/view?usp=sharing
If you prefer, my alternetive question is: how do I traduce the SQL query in the image in DAX? I was trying to replicate it with the first formula I posted, but the results are not equal.