DAX measure error date is incompatible with int.

[Anonymous]

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

[Anonymous]

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

[johnt75]

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

[Anonymous]

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?

[johnt75]

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

[Anonymous]

Nope still the same

[johnt75]

Is the date table marked as a date table?

Do any other tables filter the date table ?

[Anonymous]

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?

[johnt75]

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

[Anonymous]

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 🙂

[johnt75]

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

[Anonymous]

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

[johnt75]

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