Forum Discussion
Calculate variable with if statement based on date without value
Hello everyone, Im kinda new to the DAX and im struggleing to calculate the final cost price of a product based on some variables. What i have fixed is following: So if there is a transaction...
- hi everyone & HotChilli,My collegeau and i have solved the issue for the requirement. the main root cause is the data type of the column EndingDate which has records with value 1753/01/01. After initial load to Power BI file, this record is treated as a date. This created a mismatched with my variables in the measure. So i changed in my SQL view with nullif statement to make the records as BLANK.The following dax code works :Total CostPrice Products =VAR WithDate =MAX ( Fact_ValueEntry[PostingDate] )VAR NoDate =MIN (Dim_PurchasePrice[EndingDate])VAR Product =MAX ( Fact_ValueEntry[ItemFK] )VAR StartingDatePurchase =CALCULATE(MAX(Dim_PurchasePrice[StartingDate]),Dim_PurchasePrice[StartingDate] <= WithDate)VAR CostPriceWithDate =CALCULATE (MAX ( Dim_PurchasePrice[DirectUnitCost] ),Dim_PurchasePrice[ItemFK] = Product,Dim_PurchasePrice[StartingDate] = StartingDatePurchase,NOT( Dim_PurchasePrice[ItemFK] IN {"INKT","GLUE"}))VAR CostPriceInk =CALCULATE (MAX ( Dim_PurchasePrice[DirectUnitCost] ),Dim_PurchasePrice[ItemFK] = "INKT")VAR CostPriceGlue =CALCULATE (max ( Dim_PurchasePrice[DirectUnitCost] ),Dim_PurchasePrice[ItemFK] = "LIJM")RETURNCostPriceWithDate + CostPriceInk + CostPriceGlue
hi everyone & HotChilli,
My collegeau and i have solved the issue for the requirement. the main root cause is the data type of the column EndingDate which has records with value 1753/01/01. After initial load to Power BI file, this record is treated as a date. This created a mismatched with my variables in the measure. So i changed in my SQL view with nullif statement to make the records as BLANK.
The following dax code works :
Total CostPrice Products =
VAR WithDate =
MAX ( Fact_ValueEntry[PostingDate] )
VAR NoDate =
MIN (Dim_PurchasePrice[EndingDate])
VAR Product =
MAX ( Fact_ValueEntry[ItemFK] )
VAR StartingDatePurchase =
CALCULATE(MAX(Dim_PurchasePrice[StartingDate]),
Dim_PurchasePrice[StartingDate] <= WithDate)
VAR CostPriceWithDate =
CALCULATE (
MAX ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = Product,
Dim_PurchasePrice[StartingDate] = StartingDatePurchase,
NOT( Dim_PurchasePrice[ItemFK] IN {"INKT","GLUE"})
)
VAR CostPriceInk =
CALCULATE (
MAX ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = "INKT")
VAR CostPriceGlue =
CALCULATE (
max ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = "LIJM")
RETURN
CostPriceWithDate + CostPriceInk + CostPriceGlue