RCustomDAXFilter logic

[ldwf]

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

[v-cgao-msft · Accepted solution]

Hi @ldwf ,

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

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.

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

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

[ldwf]

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!