Measure doesn't display all cell value

[Anonymous]

I'm trying to calculate with a measure the portfolio of open orders value for a warehouse day by day, that is the value of every purchase order that has been purchased but not arrived to the warehous yet, so I made this formula with these filters: 

MEASURE=

VAR data =

MAX ( 'D - Date'[Date] )

VAR valore =

IF ( ISBLANK ( [I_Unit Cost] ), [PO_Net price], [I_Unit Cost] )

RETURN

CALCULATE (

SUMX ( 'F - Inventory', 'F - Inventory'[Quantity] * valore ),

'D - Warehouse'[Warehouse ID] = "B0",

'D - Item'[Type] = "Item",

'D - Item'[SpecialUnificato] = "Unificato",

data >= 'F - Inventory'[Created Date Time],

'F - Inventory'[Transaction type] = "Ordine fornitore",

YEAR ( 'F - Inventory'[Physical date] ) = 1900

|| 'F - Inventory'[Physical date] > data 

)

 IMAGE: https://ibb.co/YQmTtF1

The problem is that it doesn't populate every day, but only few cells. I'd expect every cell of the column to be populated. Why? Is the measure wrong or is it not right to use a measure in this case?

[amitchandak]

@Anonymous , I looked at formula and few filter are not very clear

like

YEAR ( 'F - Inventory'[Physical date] ) = 1900
|| 'F - Inventory'[Physical date] > data (date ?, is it date ?)

Post that you have commented or used OR, also not clear.

The way out is to add filters one by one check which one fails.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. And conditions you want to build formula

[Anonymous]

@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).

[Greg_Deckler]

@Anonymous Can you post sample data and expected output? Thanks.

[Anonymous]

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 = 2020

SELECT 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.DtDateTime

Here'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.