Calculate date -6 months after filter usage

[Krexx]

Hello together, i am new to PowerBI and have a question based on access to filter parameters. 

I have a date filter where i can select a period of time. However, the selected period must be calculated in the background -6 months. If the selected dateperiod in the filter is from 01/01/2020 to 31/12/2020, then the start time 01/01/2020 has to untergo an add_months(-6) function in the background.

After that i need to aggregate product numbers on a monthly basis based on the first ordered datum. For example if a product was ordered 3 times in this period (01/07/2019 - 31/12/2020) then only the frist ordered date is relevant.

However the report should only show the results from 01/01/2020 to 31/12/2020. This should lead to the fact that products that were ordered 6 months ago in the past are no longer displayed in the graphic.

How can I address the date parameters?

Kind regards

[v-easonf-msft]

Hi , @Krexx 

Could you please tell me whether your problem has been solved?For now, there is no content of description in the thread.
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
More people will learn new things here. If you haven't, please feel free to ask.It will be better if you can share a sample for testing.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Krexx , You can create a rolling formula with date calendar

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))

Date Table -https://youtu.be/Qt0TM-4H09U

In case you also want to display those 6 months refer this :

https://www.youtube.com/watch?v=duMSovyosXE

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Krexx]

@amitchandak thanks for your response. But how can i connect them to my slicer?

For example i have an table with with 2 columns (Productnumber, Orderdate). Now i use a slicer for Orderdate. 
When i filter from 01/01/2020 - 31/12/2020 then the whole table should be filtered from 01/07/2019 - 31/12/2020. Due to i want to have an -6 months function for my start_date. 

Thanks in advance

[v-easonf-msft · Accepted solution]

Hi , @Krexx 

Build  a  new calendar table as a slicer  :

Calendar = CALENDAR(DATE(2019,07,01),DATE(2021,01,01))

The create a calcualted column in the new calendar table as below:

Date-6m = DATEADD('Calendar'[Date],-6,MONTH)

 Then you can create a visual control measure and apply it in visual filters pane:

visual control = 
VAR min_selectvalue =
    MIN ( 'Calendar'[Date-6m] )
VAR max_selectvalue =
    MAX ( 'Calendar'[Date] )
VAR currentdate =
    MAX ( 'Table'[Orderdate] )
RETURN
    IF ( currentdate >= min_selectvalue && currentdate <= max_selectvalue, 1, 0 )

 

Please check the attached  sample file for more details.

If i misunderstood,please feel free to let me know.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.