Forum Discussion

abd8's avatar
abd8
Regular Visitor
1 year ago
Solved

Dynamic Dates for 13-Month Trend

Hi -   I am attempting to get a dynamic 13-month trend that will update based on the date I select in a drop-down.   For example, when I select February 2025, I want the trend to show February 20...
Need Help
  • abd8's avatar
    abd8
    Regular Visitor
    1 year ago

    This is very helpful. However, when I try to implement that code, I'm getting this error:

    USERELATIONSHIP function can only use the two columns references participating in relationship.

     

    I did tweak it slightly for my use case, so that may be what's causing it:

    13_Month_Trend =
     
    VAR NumOfMonths = -13
    VAR ReferenceDate = 'Calendar'[SelectedMonth]
    VAR PreviousDates =
        DATESINPERIOD (
            'Previous Date'[Date],
            ReferenceDate,
            NumOfMonths,
            MONTH
        )
    VAR Result =
        CALCULATE (
            SELECTEDMEASURE (),
            REMOVEFILTERS ( 'Calendar' ),
            KEEPFILTERS ( PreviousDates ),
            USERELATIONSHIP ( 'Previous Date'[Date], 'Calendar'[SelectedMonthColumn] )
        )
    RETURN Result
    • DataInsights's avatar
      DataInsights
      Icon for Super User rankSuper User
      1 year ago

      abd8,

       

      Verify the columns in the relationship between 'Previous Date' and 'Calendar'. The columns in the relationship should be used in the USERELATIONSHIP function.

      • abd8's avatar
        abd8
        Regular Visitor
        1 year ago

        Ok, I set up the relationship and that formala is now working. How do I now set up my slicer and x-axis to ensure a proper 13-month lookback on the x-axis based on the slicer selection?