Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Krexx
Helper I
Helper I

Calculate date -6 months after filter usage

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

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

 53.png

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.

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

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

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

 

 

@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

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 )

 53.png

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.