IF condition in DAX giving error “Cannot convert value 'FALSE' of type Text to type Date.”

Question

I have to create a dashboard which has one single select dropdown of dates and what-if parameter in form of slider, which has values from 0 to 24.

I want to find sum of sales from Table Fact_Sales. Sales should be by month and Year.

Sales should be calculated for the period between Start_Date and End_Date.

Now Start_Date will come from the slider which will be from the below formula:

date(year(HASONEVALUE('Date'[Distinct Delivery Date Key])),month(HASONEVALUE('Date'[Distinct Delivery Date Key]))-Cust_Key[Cust_Key Value],1)

End_Date will come from the Dropdown -->

HASONEVALUE('Date'[Distinct Delivery Date Key])

Cust_Key[Cust_Key Value] -- >> This is slider value which sliders from 0 to 24 months

The Problem :

While calculating the calculated column from the below formula, I get error as "Cannot convert value 'FALSE' of type Text to type Date."

Is Date Filtered = If(datevalue('Fact Sale'[Delivery Date Key])>=datevalue(date(year(HASONEVALUE('Date'[Distinct Delivery Date Key])),month(HASONEVALUE('Date'[Distinct Delivery Date Key]))-Cust_Key[Cust_Key Value],1)) && datevalue('Fact Sale'[Delivery Date Key]) <= datevalue(HASONEVALUE('Date'[Distinct Delivery Date Key])),1,0)

enter image description here

I have attached error , dropdown and slider screenshots.

Thanks in advance..

Update :

I have updated the DAX calculation from calculated column to measure. The new formula which I made is:

Total Quantity = 
                     var SValue = SELECTEDVALUE('Date'[Distinct Delivery Date Key])
return
CALCULATE(sum('Fact Sale'[Quantity]),FILTER('Fact Sale',[Delivery Date Key] >= date(year(SValue),month(SValue)-Cust_Key[Cust_Key Value],1) && 
                                                        [Delivery Date Key] <= date(year(SValue),month(SValue),1)
))

RADO RADO · Accepted Answer · 2020-07-19T21:31:20

The reason you are getting the error: function HASONEVALUE returns TRUE or FALSE. So your formula essentially reads:

... date(year(FALSE) ...

which makes no sense.

The function you were probably looking for is SELECTEDVALUE:

YEAR ( SELECTEDVALUE ( 'Date'[Distinct Delivery Date Key] ))

and to avoid writing it many times you should store it in a variable, i.e.,

VAR Selected_Date = SELECTEDVALUE ( 'Date'[Distinct Delivery Date Key] )

and then you can use Selected_Date in your formula.

I must point though that if you are trying to build a calculated column with this approach, it won't work. Calculated columns can not respond to slicers and other user actions.

IF condition in DAX giving error “Cannot convert value 'FALSE' of type Text to type Date.”

1 Answers