Forum Discussion
RCustomDAXFilter logic
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 _filterIf 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 _salesBest Regards,
Gao
Community Support TeamIf 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
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
_filterIf 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
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!
