topic Re: DAX measure error date is incompatible with int. in DAX Commands and Tips

DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 09:03:56 GMT

Hi all,

Im trying to configure the functionality where based on a selection of a month in the filter, the visual shows the previous 12 months going back from the selected month. I wrote a DAX measure to use in the visual but when using it I get the error that date is incompatible with int.

I think it has to do with the variables used but cant figure out what it exactely is. The measure used is:

12 Months back =

VAR CurrentDate = SELECTEDVALUE('dim_Date'[EOM Date], MAX('dim_Date'[EOM Date]))

VAR PreviousDate = CurrentDate -365

RETURN

CALCULATE(SUM(fact[columnl]), FILTER( dim_Date, dim_Date[Date] >= PreviousDate && dim_Date[Date] <= CurrentDate))

Any help is welcome. Thanks

Re: DAX measure error date is incompatible with int.

johnt75 — Tue, 09 May 2023 09:42:36 GMT

Try

12 Months back = VAR CurrentDate = SELECTEDVALUE ( 'dim_Date'[EOM Date], MAX ( 'dim_Date'[EOM Date] ) ) VAR Result = CALCULATE ( SUM ( 'fact'[column] ), DATESINPERIOD ( 'Date'[Date], CurrentDate, -12, MONTH ) ) RETURN Result

Re: DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 09:56:00 GMT

Results in the following error: Invalid Numeric Representation of a Date Value

Re: DAX measure error date is incompatible with int.

johnt75 — Tue, 09 May 2023 10:03:40 GMT

Try changing it to return the CurrentDate variable and see what that shows.

Re: DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 10:16:16 GMT

That returns the date time of the selected date in the filter (based on 'dim_Date'[EOM Date])

Re: DAX measure error date is incompatible with int.

johnt75 — Tue, 09 May 2023 10:20:39 GMT

Try

12 Months back = VAR CurrentDateTime = SELECTEDVALUE ( 'dim_Date'[EOM Date], MAX ( 'dim_Date'[EOM Date] ) ) VAR CurrentDate = DATE ( YEAR ( CurrentDateTime ), MONTH ( CurrentDateTime ), DAY ( CurrentDateTime ) ) VAR Result = CALCULATE ( SUM ( 'fact'[column] ), DATESINPERIOD ( 'Date'[Date], CurrentDate, -12, MONTH ) ) RETURN Result

Re: DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 10:37:49 GMT

The error is gone an the measure shows data in the visual but it's still just one point in time, corresponding with the selection in the filter. It doesn't show the previous 12 months going back from that selected date as I expected it to do..

Any ideas?

Re: DAX measure error date is incompatible with int.

johnt75 — Tue, 09 May 2023 10:41:03 GMT

Hopefully this will do it

12 Months back = VAR CurrentDateTime = SELECTEDVALUE ( 'dim_Date'[EOM Date], MAX ( 'dim_Date'[EOM Date] ) ) VAR CurrentDate = DATE ( YEAR ( CurrentDateTime ), MONTH ( CurrentDateTime ), DAY ( CurrentDateTime ) ) VAR PreviousDate = DATE ( YEAR ( CurrentDateTime ) - 1, MONTH ( CurrentDateTime ), DAY ( CurrentDateTime ) ) VAR Result = CALCULATE ( SUM ( 'fact'[column] ), DATESBETWEEN ( 'Date'[Date], PreviousDate, CurrentDate ) ) RETURN Result

Re: DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 10:54:24 GMT

Nope still the same

Re: DAX measure error date is incompatible with int.

johnt75 — Tue, 09 May 2023 11:05:05 GMT

Is the date table marked as a date table?

Do any other tables filter the date table ?

Re: DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 12:40:30 GMT

The date table is marked as a date table and ther arent any other tables that filter the date table. However the datetable is related to the fact based on a numeric datekey and not an actual date, could that be the issue?

Re: DAX measure error date is incompatible with int.

johnt75 — Tue, 09 May 2023 12:56:01 GMT

That shouldn't make any difference. Try adding REMOVEFILTERS('Date') to the CALCULATE statement.

Re: DAX measure error date is incompatible with int.

Anonymous — Tue, 09 May 2023 14:45:54 GMT

Nope, still not. It looks as if the slicer selection somehow overrules the values shown in the visual based upon the measure. I have to dig a little further into this.

Thanks for your help anyway, appreciate it 🙂