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

Hi cn4422 ,

Your current DAX formula for MinValue Visible is calculating the minimum within the current month instead of considering all selected months. The issue is likely caused by the ALLSELECTED(Datum[Month]) filter, which does not ensure that all months remain in scope correctly.

Corrected DAX Measure
Try modifying your MinValue Visible measure as follows:

MinValue Visible =
VAR CurrentCountry = SELECTEDVALUE(Countries[Country])
RETURN
CALCULATE(
MIN([FBA Switch]),
ALLSELECTED(Datum),
FILTER(ALL(Countries), Countries[Country] = CurrentCountry)
)


Explanation of Fix:
ALLSELECTED(Datum): Ensures that all months in the selected range remain in context.
MIN([FBA Switch]): Now correctly calculates the minimum value across all selected months for the same country.
FILTER(ALL(Countries), Countries[Country] = CurrentCountry): Maintains the filter on the selected country.


Expected Outcome
Now, for each row (month), the MinValue Visible column should show the same minimum value across all selected months for that country instead of just repeating FBA Switch for that specific month.

 

Please mark this post as solution if it helps you. Appreciate Kudos.

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

    Hi FarhanJeelani ,

    thanks for your reply and your explanation.

     

    When I use your measure I get the following error message:

    The ‘MIN’ function only accepts a column reference as argument no. 1.

     

    That's why I tried MINX.