Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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]
)
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?
Yes sir my bad
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]
)
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |