Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Hydrarian
Helper I
Helper I

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 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?

4 REPLIES 4
amitchandak
Super User
Super User

@Hydrarian , 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

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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):

cc.PNG
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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.