Dynamic Dates for 13-Month Trend

[abd8]

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.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar question in the attached PBI file.

[abd8]

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]

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.

[DataInsights]

@abd8,

The calculated table you created in step 5 is unable to recognize user-specified filters in Report view (e.g., slicers). See link below for a dynamic solution:

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ 

[abd8]

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]

@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]

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]

@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.