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 meas...
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?

 

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

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

      Nope still the same

       

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

        Is the date table marked as a date table?

        Do any other tables filter the date table ?