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
    Super 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?

      • DataInsights's avatar
        DataInsights
        Super User
        1 year ago

        abd8,

         

        The slicer would be based on a 'Calendar' field such as Year Month (you should be able to control all visuals with this slicer). The x-axis would use a 'Previous Date' field.