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

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.

5 Replies

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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