Using date parameters in a DAX measure

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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

[ibarrau]

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.

[Anonymous]

I defined both the parameters' type as a Date, and have added the Datevalue function to the other date field but still get the same error message!  Have also tried SELECTEDVALUE instead of VALUES....

[ibarrau]

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.

[Anonymous]

Hi,

The date field in the data is shown below - I see that there is a calender icon so it is a recognised date format.  And then the Parameter screen snip - selected as Date type.  

[ibarrau]

This doesn't make sense at all. You are adding parameters on edit query that will ask for them at the moment of loading data and you want them to use it on DAX.

Can you show us the Parameter From table and Parameter To? How are you adding those fields to the page in the report?

Because it seems you have a table there created with the edit query parameter.

Regards,

[Anonymous]

Hi,

Perhaps I am going about this incorrectly.  What I wanted to achieve was a date field parameter which a user selects from a drop down list which is not in the main tables (disconnected).  The Dax expression would then use this date selection against some logic on the date field within the table.

There are requirements for different types of totals to respond differently to the same date selected in the parameter, which is why I can't simply use the date field in the table itself as the drop down list for a user.

[v-lili6-msft · Accepted solution]

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

[feelie75]

YES!!! Been searching for days for this, and it works!

I have 100 locations in my warehouse, A1 thru Z1 or whatever, and I wanted to see what locations have been cycle-counted during a date range. But I ALWAYS want to see all 100 locations.

Using a date slicer was causing locations that weren't counted within the date range to simply not show up. Using your technique I can maintain all 100 locations always showing, and the Cycle-Count # and Date will simple appear or disappear as I move the date range, leaving the rest of the row in tact! brilliant. Thanks!!

[Anonymous]

Hi,

Many thanks to both of you for providing solutions - I think it has worked but it has absolutely killed performance to the point where I can't use the file in a reasonble time to continue the work which needs to be done!

[Anonymous · Accepted solution]

I have utilised "userelationship" with my existing two tables and it seems to run at a reasonable speed.

Many thanks for your help, accepting as solution.

[ibarrau]

Ok, you can try two things. One should be adding a DateTable not related with the model (due to your logic) where the date column is a range slicer. Two, adding two dropdownlist for year, month and day that won't interact between them (edit interactions to avoid them filtering).

With this two ways I think you can find a from and to date selected by the user. With the range you can use MAX MIN date to take it and with the other dropdown you can user SELECTEDVALUES.

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.