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]
    )
cn4422's avatar
cn4422
Icon for Helper V rankHelper V
1 year ago

sanalytics Thanks for your help!


sanalytics wrote:

May i ask why Country dimension is disconnected.?

Sure! 🙂

The reason is that I have provided a simplified Test-file... in the real file there isn't an actual country dimension because I get the country-data from campaigns running on for example Facebook. On Facebook I have the countries segmented with campaign-names, like [AT]Campaign_1 for a campaign running in Austria.

 

And I have created a switch-statement:

Countries = DATATABLE(
    "Country", STRING,
    "Country Name", STRING,
    {
        {"AT", "Österreich"},
        {"BE", "Belgien"},
        {"CH", "Schweiz"}
    }
)
which I then use in combination with data from the Facebook-campaigns, like ad spend to create a Matrix-Table like this:
 
 
And therefore I wanted to have the Min- and Max-Values which I can then use for conditional formatting. 🙂