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 that has a posting date that has a value between startingdate and endingdate then the Variable CostPriceWithDate will solve this.
- The final cost price for a product with a date is calculated in variable FinalCostPriceWithDate .
- Ifthere is no value on the column (Dim_PurchasePrice[EndingDate]) (see the Variable NoDate) then calculate the variable CostPriceWithoutDate. The final cost price for a product without a date is calculated in variable FinalCostPriceWithoutDate.
The requirement is: if there is no value in the column Ending Date (see image below) then use the variable FinalCostPriceWithoutDate , otherwise use the variable FinalCostPriceWithDate .
My question is how to return the correct value (based on the underneath Dax) if there is no data in a column Ending Date (see image below) I think the part of Var NoDate and my Return statement is not correct. Please help.
Kind regards
Totaal CostPrice Products =
VAR Date =
MAX ( Fact_ValueEntry[PostingDate] )
VAR NoDate =
MIN (Dim_PurchasePrice[EndingDate])
VAR Product =
MAX ( Fact_ValueEntry[ItemFK] )
VAR CostPriceWithDate =
CALCULATE (
MAX ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = Product,
Dim_PurchasePrice[StartingDate] < Date,
Dim_PurchasePrice[EndingDate] >= Date
)
VAR CostPriceWithoutDate =
CALCULATE (
MAX ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = Product,
Dim_PurchasePrice[StartingDate] < Date,
Dim_PurchasePrice[EndingDate] = NoDate)
VAR CostPriceInk =
CALCULATE (
max ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = "INK")
VAR CostPriceGlue =
CALCULATE (
max ( Dim_PurchasePrice[DirectUnitCost] ),
Dim_PurchasePrice[ItemFK] = "GLUE")
VAR FinalCostPriceWithDate = CostPriceWithDate + CostPriceInk + CostPriceGlue
VAR FinalCostPriceWithoutDate = CostPriceWithoutDate + CostPriceInk + CostPriceGlue
RETURN
IF(ISBLANK(NoDate), FinalCostPriceWithoutDate, FinalCostPriceWithDate))
- 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
4 Replies
Can you provide a test pbix please because it's a bit difficult to debug this without seeing the model/relationships?
Just fake a small example (and provide the desired result please) and link it from an external site and I'll have a look for you
Hi HotChilli ,
I have uploaded the pbix example in my public github repository. Download the file of 2,19 mb
Please try this link:
https://github.com/regazzi24/powerbi/blob/main/Example%2011102021%20v1.pbix
- 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 Anonymous ,
Very happy about your feedback.
If the problem has been solved, you can mark the correct response as the standard answer to help the other members find it more quickly. Looking forward to your reply.
Best Regards,
Henry
