Forum Discussion
Calculate Min Values within a Specific and Dynamic Date Range
I have created a static version of a date range by repeatedly using a data field in the visual filters but want to write a DAX expression to do the same thing and be dynamic. What I am trying to do is CALCULATE a minimum value of a number column, { MIN(zValus (INT) ) }, within a date range for several sites.
My current 'Filters on this visual' set up is...
- [Date] is on or after 8/31/2022 - filter data before reporting period
- [Date] is in the last 2 Years - filter out all future dates
- [Date] is not 02/02/2023 or 01/01/23 - filter out this month and last month because the report is to end the 7th of the previous month.
The above almost does this but i need to re-write it each month to get what I need. I am to use the date range in a CALCULATE function.
RP = Reporting Period date value from data.
Test Column = CALCULATE(MIN('Table'[zValus]), 'Table'[RP] > [FY Start Date] && 'Table'[RP] < [FY End Date])
I think this is close but I dont understand the error i produces...
A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Any help is greatly appreciated.
Sincere thanks Anonymous ! Although your code was not exactly what I needed, it was enough to get me where i needed to be. I will see if it does what I think it will next week. Counting that as a solution for you. This is what I ended up using to limit my data dynamically.
End Date Filter (T/F) = 'Table'[RP] <= EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),7),-2)// Looking at the reporting period (RP) entered on the form... RP is less than or equal to the current year and month two months before the 7th of month... after the 7th of this month the data ends on the prior month.
2 Replies
Sincere thanks Anonymous ! Although your code was not exactly what I needed, it was enough to get me where i needed to be. I will see if it does what I think it will next week. Counting that as a solution for you. This is what I ended up using to limit my data dynamically.
End Date Filter (T/F) = 'Table'[RP] <= EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),7),-2)// Looking at the reporting period (RP) entered on the form... RP is less than or equal to the current year and month two months before the 7th of month... after the 7th of this month the data ends on the prior month.