Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure doesn't display all cell value

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...
General Comment
Need Help
Show and Tell
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.