Range (inclusive) Operator issue in Report builder

[Anonymous]

Hello all,

New to Power BI and learning something new everyday.

I am having an issue when i use the range inclusive operator in the Report builder.

I created a dataset in Power Bi desktop with Oracle as the source. Using this dataset as the source, I created a report in the report builder. I used the Query designer in which i drag and drop the dimensions and measure and in the filter i used a the TRAN_DATE filed with Range (inclusive) Operator. When i run the report and select the From and To dates in the parameters, I am getting the below error:

Query (1, 142) DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
----------------------------
Query execution failed for dataset 'DataSet1'.
----------------------------
An error has occurred during report processing.
----------------------------
An error occurred during local report processing.

Here is the Query :

DEFINE VAR vFromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1 = IF(PATHLENGTH(@FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE) = 1, IF(@FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE <> "", @FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, BLANK()), IF(PATHITEM(@FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, 2) <> "", PATHITEM(@FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, 2), BLANK()))
VAR vFromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1ALL = PATHLENGTH(@FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE) > 1 && PATHITEM(@FromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, 1, 1) < 1
VAR vToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1 = IF(PATHLENGTH(@ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE) = 1, IF(@ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE <> "", @ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, BLANK()), IF(PATHITEM(@ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, 2) <> "", PATHITEM(@ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, 2), BLANK()))
VAR vToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1ALL = PATHLENGTH(@ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE) > 1 && PATHITEM(@ToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE, 1, 1) < 1
EVALUATE SUMMARIZECOLUMNS('Refund Check Liability - US - On Demand (R3-P3-179A)'[CHANNEL_ID], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[CHANNEL_NAME], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[STORE], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[NAME], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[ADDR1], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[ADDR2], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[CITY], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[STATE], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[POSTAL_CODE], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[HOME_PHONE], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[TRAN_DATE], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[POS_TRAN], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[REG#], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[CASHIER], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[TRAN_SEQ_NO], 'Refund Check Liability - US - On Demand (R3-P3-179A)'[TENDER_AMT], FILTER(VALUES('Refund Check Liability - US - On Demand (R3-P3-179A)'[TRAN_DATE]), (vFromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1ALL || 'Refund Check Liability - US - On Demand (R3-P3-179A)'[TRAN_DATE] >= DATEVALUE(vFromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1) + TIMEVALUE(vFromRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1)) && (vToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1ALL || 'Refund Check Liability - US - On Demand (R3-P3-179A)'[TRAN_DATE] <= DATEVALUE(vToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1) + TIMEVALUE(vToRefundCheckLiabilityUSOnDemandR3P3179ATRANDATE1))))

[maltamar]

I´m having the issue of the date range not being inclusive, I select the FROM date and it will give me data just for that date and not the date range selected From-To 

Has anybody has this issue?

[ShaunFenchel]

I am having the same issue.  I have a date field formatted as a date in my Power BI Dataset.  When I build a query using the date field and use "Range (Inclusive)", I get this same issue if the parameter is formatted as a date/time.  If I change it from "Range (Inclusive)" to "Equals", it will work with the parameter formatted as a date/time.  I want the parameters formatted as date/time so the calendar picker is available, but it will not work with a range.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to the information you provided, for this type of error it is caused by a wrong setting of the field's type. The values to be compared need to have the same data type.

You use Format function to change the data type or just the date type on the desktop menu.

For example:

Column = FORMAT('Table'[Date],"yyyy-mmmm")

related document link:

Power BI Date Formatting | How to Change Date Format in Power BI? (wallstreetmojo.com)

Using the Field list in Power BI Desktop - Power BI | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.