Creating a custom date range based on a given date input

[anthonydiclemen]

Hi Everyone,

I would like to create a specified date range depending on user input. This could be done in a slicer. Here's an example. I want to always show the previous 6 months of data based on a date:

IE. User inputs 6/01/2022 and the visualizations would then refresh to show the previous 6 months.

Any help would be greatly appreciated.

Thanks!

Anthony

[v-yanjiang-msft]

Hi @anthonydiclemen ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft · Accepted solution]

Hi @anthonydiclemen ,

According to your description, I create a sample.

Here's my solution.

1.Create a new Date table, don't make relationship between the two tables.

Date = VALUES('Table'[Date])

2.Create a measure.

Check =
IF (
    ISFILTERED ( 'Date'[Date] ),
    IF (
        MAX ( 'Table'[Date] )
            IN DATESINPERIOD ( 'Date'[Date], SELECTEDVALUE ( 'Date'[Date] ), -6, MONTH ),
        1
    ),
    1
)

Put the measure in the visual filter and select its value to 1, then put the new Date column in the slicer, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Download the workbook from there and do not open it.  Open PowerBI Desktop and import this workbook into that. 

[anthonydiclemen]

Hi,

Thank you. This seems like a great solution for Excel but not exactly sure how I can dynamically use this in Power BI. Any guidance would be appreciated

[Ashish_Mathur]

Hi,

As mentioned in my previous message, open PowerBI Desktop and import the MS Excel workbook.  Once imported, please spend some time in studying my solution.