Minif function that compares the date of a row to the date of all other rows

[claudedubois]

Hey everyone, 

I'm trying to populate the right hand column below. 

It calculates the minimum quantity purchased in the last twelve months from the date of every row.  

In excel, the function would be: minif (quantity column, description of all rows = description of current row, date of every row <= date of current row, date of every row >= (date of current row -365)

Struggling to translate the function to PBI? I guess it's shouldn't be a measure as every row has a separate value? Should it be a new column?

Any help would be greatly appreciated! thank you. 

[claudedubois]

Hi @tamerj1 

This is super super helpful! Thanks a lot for the help, saved me 1 day of useless work trying to figure this out. 

I do have another function that is similar but a bit more complex? Can I pls ask for it?

[tamerj1]

@claudedubois 
Yes, please go ahead.

[claudedubois]

Yes sir my bad

[tamerj1 · Accepted solution]

@claudedubois 

Following is a calculated column 

Min QTY Last 12 Months =
MINX (
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Description] ) ),
'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[Date]
> EARLIER ( 'Table'[Date] ) - 365
),
'Table'[Quantity]
)

[tamerj1]

Hi @claudedubois 

Coffee 10-Dec-22 should be 9?