Forum Discussion

jayala's avatar
jayala
Frequent Visitor
6 years ago
Solved

Fixed time range

Hi guys,   I have a dataset that I need to filter by a fixed windows range time of 12 months.   I tried using two slicers: one with the "After" option for the Start date and a second one using a ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi jayala ,

    You just want to show the values for 12 months after the selected date. Right? If so, I think you could create a measure like below:

    Selected Date + 365 =
CALCULATE (
    SUM ( 'Table'[ Sales] ),
    FILTER ( 'Table', 'Table'[Date] >= MIN ( 'Date'[Date] ) + 365 )
)

    If you want to show the values which is between selected date and 12 months after that, please try this:

    selected date to Selected Date + 365 =
CALCULATE (
    SUM ( 'Table'[ Sales] ),
    FILTER (
        'Table',
        'Table'[Date] >= MIN ( 'Date'[Date] )
            && 'Table'[Date]
                <= MIN ( 'Date'[Date] ) + 365
    )
)

     

    Best Regards,

    Xue Ding

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

When you use date in slicer you have the option to use the Relative option. The same is also available if you drag date into filter pane.

 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • jayala's avatar
    jayala
    Frequent Visitor
    6 years ago

    Hi amitchandak thank you for your answer but I need something a little bit different. Let's assume that I select a specific Start Date present in my data set, I need to see only data for 12 months after the chosen date.

     

    The relative option seems to be relative to the current date. Am I right?

     

    Thank you again.

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi jayala ,

      You just want to show the values for 12 months after the selected date. Right? If so, I think you could create a measure like below:

      Selected Date + 365 =
CALCULATE (
    SUM ( 'Table'[ Sales] ),
    FILTER ( 'Table', 'Table'[Date] >= MIN ( 'Date'[Date] ) + 365 )
)

      If you want to show the values which is between selected date and 12 months after that, please try this:

      selected date to Selected Date + 365 =
CALCULATE (
    SUM ( 'Table'[ Sales] ),
    FILTER (
        'Table',
        'Table'[Date] >= MIN ( 'Date'[Date] )
            && 'Table'[Date]
                <= MIN ( 'Date'[Date] ) + 365
    )
)

       

      Best Regards,

      Xue Ding

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