CODEWITHSUNDEEP
powerbidax
sam
sam

Reputation: 1304

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

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

enter image description here

enter image description here

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

enter image description here

enter image description here

Upvotes: 0

Views: 5262

Answers (1)

RADO
RADO

Reputation: 8148

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.

Upvotes: 1

Related Questions