Need Help with Dax

[mjanecek]

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.

[ppm1 · Accepted solution]

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]

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...

[mjanecek]

Thank you so much!  This works well.

[Mikelytics]

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]

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.