How to get a fixed period (previous N and Next N period) of value based on a single period selection

[Roy_tap]

Hi,

I have 2 tables, one is period table and one is fact table with period

Period table contains Period column
2023.01 - 2024.12

Fact table contains the following
Period      Value
2023.09       3
2023.10       2
2023.11       4
2023.12       7
2024.01       5
2024.02       3
2024.03       2
2024.04       7
2024.05       6
2024.06       2

In my report, I have a Period Slicer (can be from Period table or Fact table) which can only single select a Period e.g. 2024.03

How can i get a line chart that will display a fixed Last 5 period and Next 2 period value i.e. 2023.10 - 2024.05? 
When i select 2024.04, it will display the period from 2023.11 - 2024.06.

Thanks

[qqqqqwwwweeerrr · Accepted solution]

Hi @Roy_tap 

This can be achived by creating follwoing measure it is just one thing i taken date format little different i guess which should not be an issue here is my approahc to get the required things 
this measure needs to be created in your period table 

Value in certain period =

 var maxdate = MAX(Period[Period])

 var nexttwomonth = EOMONTH(maxdate,+2)

 var previousfourmonths = EOMONTH(maxdate,-6)

 var resulst =

               CALCULATE(SUM('Fact'[Values]),

                                         FILTER('Fact','Fact'[Period] >= previousfourmonths && 'Fact'[Period]<= nexttwomonth))

    RETURN resulst
Now in filter use the period from period table 
if you use this confreguration, with little change you should get the desired output

Here is another way of solving the same problem : https://youtu.be/uQPXYW79m3A?si=KmXniEIVGNy4KHzC

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards