Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX measure error date is incompatible with int.

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

12 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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?