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 2024-February 2025 or when I select January 2025, I want to see January 2024 - January 2025, etc. 

 

I can't use a date range filter due to other visuals on the page that are only for the specific reporting month. Also I don't want to allow free range filter, only 13 months back from the month selected.

 

Based on some googling / copilot help I completed the following steps, but the range is still not coming through correctly. Any help greatly appreciated!

 

Step 1: Create SelectedMonth Measure on 'Calendar' Table

SelectedMonth = SELECTEDVALUE('Calendar'[Year Month])

Step 2: Create SelectMonthColumn

SelectedMonthColumn = [SelectedMonth]
Step 3: Create StartDate Measure on 'Calendar' Table
StartDate =
DATE(YEAR([SelectedMonth]) - 1, MONTH([SelectedMonth]), 1)
Step 4: Create StartDateColumn
StartDateColumn = [StartDate]
Step 5: Create '13_Month_Trend' table with start date and end date dynamic based on measures / columns created in steps 1-4. This is where things appear to be going wrong, with this table returning dates Jan 2024 - Dec 2025.
13_Month_Trend =
VAR EndDate = MAX('Calendar'[SelectedMonthColumn])
VAR StartDate = EDATE(EndDate, -12)
RETURN
    ADDCOLUMNS (
        CALENDAR (StartDate, EndDate),
        "Year", YEAR([Date]),
        "Month Number", MONTH([Date]),
        "Year Month", FORMAT([Date], "YYYY-MM"),
        "Day", DAY([Date]),
        "Day of Week", WEEKDAY([Date]),
        "Quarter", QUARTER([Date]),
        "Quarter Year", YEAR([Date]) & " Q" & QUARTER([Date])
    )
 
 
 
Here is a screenshot of the results I'm getting. When filtered for February 2025, I would expect the table to start at 2/2024 and end with 2/2025. However, it just showed Jan 2024 - Dec 2025, no matter what i select in the dropdown.

 

 

 

 

 

Need Help

8 Replies

    • 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

    I also noticed that the date range is different depending on whether I display as just the date or the date hierarchy. Does anyone know why this may be?

    • v-saisrao-msft's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi abd8,
      I wanted to check if you had the opportunity to review the information provided by Ashish_Mathur. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
      Thank you.