Forum Discussion
Helper VCalculating the Minimum Value Across Multiple Months
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] )
Helper VHi sanalytics ,
thanks for your reply!
Unfortunately, it doesn't work and I think it's because [FBA Switch] is a measure and not in a table.
I have created a Test-Power BI file where I inserted the test-data.
(It's a dropbox link - just open in a new tab - you don't need to register, you can download the file regardless).
Maybe you could have a look?
Thanks!
Super UserFirst, clean up your Spend/Country measures. What you are doing with [Spend FBA AT], [Spend FBA BE], [Spend FBA CH], and [Spend FBA Switch] we usually achieve with relationships:
1) create a relationship between Countries -1--M-> Spend (you already have dates related to spend, we are doing the same here)
2) now you can just have the following measure that automatically is filtered by Countries in your visuals:
Spend FBA = SUM( Spend[Spend] )
Now, you can do something like this to get the min spend by country:
Spend FBA Country Min =
VAR _iter =
GENERATE(
VALUES( Countries[Country] ),
ALLSELECTED( DateTable[Date_DateTable].[Monat] )
)
RETURN
MINX(
_iter,
VAR _thisMo = [Date_DateTable].[Monat] RETURN
IF(
NOT ISEMPTY( Spend ),
CALCULATE(
[Spend FBA],
DateTable[Date_DateTable].[Monat] = _thisMo,
REMOVEFILTERS( DateTable )
)
)
)
- cn4422
MarkLaf Thanks for your reply and your suggested solution!
MarkLaf wrote:First, clean up your Spend/Country measures. What you are doing with [Spend FBA AT], [Spend FBA BE], [Spend FBA CH], and [Spend FBA Switch] we usually achieve with relationship:
That's because of the simplied test-file. In the actual file I don't have columns with countries and spend which I could conntect. I get the data with a connector out of Facebook, for example, and there I only have campaigns that I can attribute to countries based on the campaign name.