cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ldwf
Helper I
Helper I

RCustomDAXFilter logic

Hello, I have a query parameter (not report parameter) where the user selects a date from the dropdown.  I want to be able to retrieve the past 13 months of data based on the date selected in the prompt.  Currently my DAX query is set to search where the date is equal to the date in the prompt, but I want to modify the DAX code to search where the data is for the past 13 months, so if the user selects October, 2022 in the dropdown, the results should be between October, 2021 and October, 2022.  The DAX code is currently just searching where the date is 'equal to'.  it looks like this RCustomDAXFilter(@DimDateBK,EqualToCondition,[Dim Date].  How do I modify the code to filter where the date is between the date selected minus 13 months and the date selected?  Thanks

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @ldwf ,

You need a calendar table with no relationship between the fact table:

vcgaomsft_0-1669269332929.png

Then please new a measure:

Measure = 
VAR _max_date = MAX('Calendar'[Date])
VAR _min_date = EDATE(_max_date,-13)
VAR _filter = IF(MAX('Table'[Date])>_min_date&&MAX('Table'[Date])<=_max_date,1)
RETURN
_filter

If the visual has a date axis, you can use it as a filter.

vcgaomsft_1-1669269561669.png

For a single measure, you can pass these two variables into the filter parameter like this.

Sales = 
VAR _max_date = MAX('Calendar'[Date])
VAR _min_date = EDATE(_max_date,-13)
VAR _sales = CALCULATE(SUM('Table'[Sales]),'Table'[Date]>_min_date&&'Table'[Date]<=_max_date)
RETURN
_sales

vcgaomsft_2-1669269676805.png

 

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @ldwf ,

You need a calendar table with no relationship between the fact table:

vcgaomsft_0-1669269332929.png

Then please new a measure:

Measure = 
VAR _max_date = MAX('Calendar'[Date])
VAR _min_date = EDATE(_max_date,-13)
VAR _filter = IF(MAX('Table'[Date])>_min_date&&MAX('Table'[Date])<=_max_date,1)
RETURN
_filter

If the visual has a date axis, you can use it as a filter.

vcgaomsft_1-1669269561669.png

For a single measure, you can pass these two variables into the filter parameter like this.

Sales = 
VAR _max_date = MAX('Calendar'[Date])
VAR _min_date = EDATE(_max_date,-13)
VAR _sales = CALCULATE(SUM('Table'[Sales]),'Table'[Date]>_min_date&&'Table'[Date]<=_max_date)
RETURN
_sales

vcgaomsft_2-1669269676805.png

 

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Gao, thanks for the response.  I should have explained better.  I am not able to upload photos per company policy. This is a Paginated Report built against a data set, not native Power BI report.  I click on Query Designer to drag all the columns to the page and build my query parameters (i.e, not report parameters).  When I add my date parameter, I have options in the drop down of 'equal to', 'not equal to', 'contains', 'begins with', 'range (inclusive)', 'range (exclusive)', and 'Custom'.  I want the date query parameter such that when the user selects a date, the  returnquery should return 13 months of data, ending on the date they select in the parameter, so if they select 10/31/22, the results will be 10/31/21 thru 10/31/22.  I don't wan the user to have to select a range if I don't have to.  Given the options in the parameter drop down, I don't see how to do this.  I thought about modifying the code of the resulting query statement (it begins with EVALUATE SUMMARIZECOLUMNS).  I see logic in the code that says 'RCustomDaxFilter(@Date,EqualtoCondition'.  I thought about changing the 'EqualtoCondition' to something like 'Between' , but nothing works.  Thanks for any assistance!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.