Rolling +- 6 months in line chart

[Iwin]

Hi,

I want to show last 6 months and next 6 months data on selection in line chart  based on below scenarios:

1)If no month is selected it should data based on max date in the data.

2)if any month is selected it is should not only show that month's data but also past and next 6 months data.

I used below expression but on selecting a month ,that month's data is correctly shown rest all months are coming as 100 

sum({<Month=,Year=,Date={">=$(=addmonths(max(Date),-6))<=$(=addmonths(Max(Date),6))"}>}sales)

What could be the issue here?

[Andrei_Cusnir · Accepted solution]

Hello, 

Your approach seems to be correct and I have followed a similar approach as well, for my example. Maybe the following steps can help you with your use case scenario:

1. I have the following dataset:

2. I have then created an Date picker object with "Single date / interval" checked. This will allow only one value to be selected from the dataset.

3. Then I have created a Line chart with dimension set to "Date"

4. The measure set to:

If(
  GetFieldSelections(Date), 
  Sum( {<Date={">=$(=AddMonths(GetFieldSelections(Date), -6))<=$(=AddMonths(GetFieldSelections(Date), 6))"}>} Value), 
  Sum( {<Date={">=$(=AddMonths(Max(Date), -6))<=$(=AddMonths(Max(Date), 6))"}>} Value)
)

5. And as title I have set the expression:

=If(
  GetFieldSelections(Date), 
  'From: ' & AddMonths(GetFieldSelections(Date), -6) & ' to: ' & AddMonths(GetFieldSelections(Date), 6) & ' based on date: ' & GetFieldSelections(Date),
  'From: ' & AddMonths(Max(Date), -6) & ' to: ' & AddMonths(Max(Date), 6) & ' based on date: ' & Max(Date)
)

This will allow us to check if the selected dates within the set analysis are the correct ones. Here are the outputs:

6. When NO date is selected:

As you can see, the Max(Date) is 9/1/2021 so the starting date is 3/1/2021 and the end date is 3/1/2022. The values displayed on the graph are also correct, based on the loaded dataset.

7. When a date is selected:

As you can see, the selected date value is 5/1/2019 so the starting date is 11/1/2018 and the end date is 11/1/2018. The values displayed on the graph are also correct, based on the loaded dataset.

Maybe you can try this approach so you could also easily troubleshoot the values on your side. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.