Need Help with Dax

I have a measure (Avg Premium) that is filtered by month for all of 2022.

Here is a visual of the months and that measure.

I am trying to write a measure that will allow me to pull out the month name and year of the month with the lowerst value of [Avg Premium].

I wrote the below measure, and its retrurning a value on the line with the month (2/22) with the lowest value of [Avg Premium], but Accouting month name and year are returning as 12(Dec) & 23, which is not even selected in the visual the MAXX statement is iterating though.

testmonth =

var premsum = SUMMARIZE(ALLSELECTED('Calendar'),'Calendar'[Accounting Month],"Avg Premium",[Avg Premium])

var curprem = [Avg Premium]

var minprem = MINX(premsum,[Avg Premium])

RETURN

MAXX(

FILTER(ALLSELECTED('Calendar'[Accounting Month]),

curprem = minprem

),FORMAT('Calendar'[Accounting Month],"mm") & " " & FORMAT('Calendar'[Accounting Month],"yy")

)

I need to figure out how to return the value of the accounting month and year on the line with the lowest value of the Avg Premium Measure.

Need Help

ppm1

3 years ago

You can use a DAX pattern like this to return your max month in a card visual. Update it with your actual table, column, and measure names.

MaxMonth =
VAR vSummary =
    ADDCOLUMNS ( ALLSELECTED ( 'Date'[YearMonthShort] ), "cQty", [Total Qty] )
VAR vTop1 =
    TOPN ( 1, vSummary, [cQty], DESC )
RETURN
    MAXX ( vTop1, 'Date'[YearMonthShort] )

Pat

5 Replies

ppm1
Solution Sage
3 years ago
You can use a DAX pattern like this to return your max month in a card visual. Update it with your actual table, column, and measure names.
MaxMonth = VAR vSummary = ADDCOLUMNS ( ALLSELECTED ( 'Date'[YearMonthShort] ), "cQty", [Total Qty] ) VAR vTop1 = TOPN ( 1, vSummary, [cQty], DESC ) RETURN MAXX ( vTop1, 'Date'[YearMonthShort] )
Pat
- mjanecek
  Helper I
  3 years ago
  Thank you so much! This works well.
- mjanecek
  Helper I
  3 years ago
  Can you tell me why my sort in the below code is not working right?
  
  EVALUATE
  VAR vSummary =
      ADDCOLUMNS ( ALLSELECTED  ( 'Calendar'[Accounting Month] ), "Prem", [Avg Premium] )
  VAR vTop1 =
  
          TOPN ( 10 , vSummary,[Prem],DESC )
  
  
  VAR vMaxmonth = maxx(vTop1,[Prem])
  RETURN vTop1
  
  The premium clearly is not sorting the top 10 values in DESC order. Have not been able to figure out why...
Mikelytics
Resident Rockstar
3 years ago
Hi mjanecek ,

I tried to replicate your usecase. Please find a suggestion below.

My data model

Formula Average
Average | Value = AVERAGE('Fact_Sales'[Values])

Formula Monthly Min Period
Iterator | Period with Min Value = var _current = [Average | Value] var _MonthlyMinValue = CALCULATE( MINX( VALUES(DimDate[MonthWithYear]), CALCULATE([Average | Value]) ), ALLSELECTED(DimDate) ) Return IF(_current=_MonthlyMinValue,SELECTEDVALUE(DimDate[MonthWithYear]))

Result

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
- mjanecek
  Helper I
  3 years ago
  Hi: This works great when there is a month on a visual, but when I try to put it into a scorecard, I get no value. How would I adapt the measure to allow the max month and year to display in a scorecard that did not have the month and year in the filter context.

Forum Discussion

Need Help with Dax