Forum Discussion
Using date parameters in a DAX measure
Hi,
I have created a "Date From" and "Date To" parameter which a user chooses from in a filter on a report. I then have a measure which is supposed to filter using the parameter to sum amounts within this chosen period. However I get an error message as per below:
dax comparison operations do not support comparing values of type date with values of type text
The Dax used is below - the paramters are already selected to Tpe "Date" so I can;t understand what is going wrong! Any help greatly appreciated!
Answer = calculate(sum('Data'[Amount]),filter('Data','Data'[Date]>=values('Parameter From'[Period From])&&'Data[Date]<=values('Parameter To'[Period To])))
hi Anonymous
For your requirement, there is another simple way for you to refer to:
Step1:
Create a date table, then create a relationship with "Data" table by date column. Keep the relationship is Inactive.
Step2:
Then use USERELATIONSHIP Function to create a measure as below:
Answer = CALCULATE(SUM(Data[Amount]),USERELATIONSHIP(Data[Date],'Date'[Date]))Step3:
Now use [Date] from date table as a slicer in Between mode.
Result:
and here is sample pbix file, please try it.
Regards,
Lin
11 Replies
Hi, It's an interesting way to use parameters. Normally I would recommend selectedvalues but the problem here is that the parameter may be a text. Did you try to convert the value to date with function like DATEVALUE or CONVERT?
https://docs.microsoft.com/en-us/dax/datevalue-function-dax
https://docs.microsoft.com/en-us/dax/convert-function-dax
Hope this helps,
Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ok, here is a stupid question but it can work, is your Date[date] really a date instead of a text?
Can you show pictures of the what if parameters? maybe we can replicate that for us and try to help you with the same scenario.
