topic Re: Minif function that compares the date of a row to the date of all other rows in DAX Commands and Tips

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

claudedubois — Thu, 26 Jan 2023 16:58:11 GMT

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.

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

tamerj1 — Thu, 26 Jan 2023 17:19:25 GMT

Hi claudedubois

Coffee 10-Dec-22 should be 9?

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

claudedubois — Thu, 26 Jan 2023 17:43:00 GMT

Yes sir my bad

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

tamerj1 — Thu, 26 Jan 2023 18:11:23 GMT

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]
)

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

claudedubois — Mon, 30 Jan 2023 06:42:47 GMT

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?

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

tamerj1 — Mon, 30 Jan 2023 07:39:07 GMT

claudedubois
Yes, please go ahead.