Calculate Min Values within a Specific and Dynamic Date Range

[ghutchins]

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.

FY Start Date = DATE(2022,8,31) - I can update this manual since it is a yearly thing

FY End Date = TODAY() - 30 - This is close but I need to dynamically end on the 7th of the previous month

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.

[Anonymous · Accepted solution]

Would you try this : FY End Date = EOMONTH(TODAY(),-1) - 7

This should calculate the end date dynamically

And then use the updated formula in the CALCULATE function:

Test Column = CALCULATE(MIN('Table'[zValus]), 'Table'[RP] > [FY Start Date] && 'Table'[RP] < [FY End Date])

[ghutchins · Accepted solution]

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.

[Anonymous · Accepted solution]

Would you try this : FY End Date = EOMONTH(TODAY(),-1) - 7

This should calculate the end date dynamically

And then use the updated formula in the CALCULATE function:

Test Column = CALCULATE(MIN('Table'[zValus]), 'Table'[RP] > [FY Start Date] && 'Table'[RP] < [FY End Date])