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?

    • DataInsights's avatar
      DataInsights
      Icon for Super User rankSuper 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.