Forum Discussion

anthonydiclemen's avatar
anthonydiclemen
Regular Visitor
4 years ago
Solved

Creating a custom date range based on a given date input

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...
Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

anthonydiclemen's avatar
anthonydiclemen
Regular Visitor
4 years ago

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's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.