Forum Discussion
cn4422
Helper V
Helper VCalculating the Minimum Value Across Multiple Months
Hi, I want to calculate the min-value for multiple months, so that for each month, the min-value reflects the actual minimum value across all selected months. I currently have this formula fo...
Ok, so it turns out that expanding the context within an "X" function get hideously complex. I got something that works though. Some extra notes: You really should replace the "Month" with a month-year column. Also, you should add an IF statementment to make sure not to return values outside the selected date range.
Min Value = MINX( SUMMARIZE( CALCULATETABLE(VALUES(DateTable[Month]),ALL(DateTable), TREATAS(ALLSELECTED(DateTable[Date_DateTable]),DateTable[Date_DateTable])) ,[Month] ,"Spend",VAR sm=[Month] RETURN CALCULATE([Spend FBA Switch], ALL(DateTable), DateTable[Month]=sm)) ,[Spend] )
sjoerdvn
Solution Sage
Solution SageHi,
Had another look into it, and it turns out it can actually be a bit less complex. Also, the "IF" to supress results outside the date range in scope is best implemented at the highest level, so not inside the MINX.
IF (AND(
MIN(DateTable[Date_DateTable])<=MAXX(ALLSELECTED(DateTable[Date_DateTable]),[Date_DateTable]),
MAX(DateTable[Date_DateTable])>=MINX(ALLSELECTED(DateTable[Date_DateTable]),[Date_DateTable])
),
MINX(
SUMMARIZE(
CALCULATETABLE(ALLSELECTED(DateTable[Month]))
,[Month]
,"Spend",VAR sm=[Month] RETURN CALCULATE([Spend FBA Switch], ALL(DateTable), DateTable[Month]=sm))
,[Spend]
)
)