Forum Discussion

cn4422's avatar
cn4422
Icon for Helper V rankHelper V
1 year ago
Solved

Calculating 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...
  • sjoerdvn's avatar
    sjoerdvn
    1 year ago

    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]
    )
sanalytics's avatar
sanalytics
Icon for Super User rankSuper User
1 year ago

Hello cn4422 
You can use below any of the measure for min value.

Min Value = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
    ALLEXCEPT('Table','Table'[Country] )
)
Min Value 2 = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
    ALL( 'Table' ),
    VALUES( 'Table'[Country] )
)
Min Value 3 = 
CALCULATE(
    MIN( 'Table'[FBA Switch] ),
WINDOW(1,ABS,1,REL,
SUMMARIZE( ALLSELECTED( 'Table'), 'Table'[Country], 'Table'[Monat] ),
ORDERBY( SUM('Table'[FBA Switch]),ASC), PARTITIONBY( 'Table'[Country] )
) )

 

Below screenshots

 Hope it helps

Let me know if you need pbix file.

 

Regards

sanalytics