Forum Discussion

Dizzy's avatar
Dizzy
Frequent Visitor
2 years ago
Solved

Get First Value on Matrix

Dear all,   I'm currenty working on issue to get only first value on Marix within Month, I used Firstnonblankvalue but I get the result without month, as you can see below I want to get only the ye...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from tamerj1 , please allow me to provide another insight: 

    Hi  Dizzy ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create measure.

     

    Measure =
var _date=
MINX(
    FILTER(ALL('Table'),
    'Table'[Account Number Name CRM]=MAX('Table'[Account Number Name CRM])&&
    'Table'[Month_Year]=MAX('Table'[Month_Year])&&
    'Table'[Value]<>BLANK()),[Date])
return
SUMX(
    FILTER(ALL('Table'),
    'Table'[Account Number Name CRM]=MAX('Table'[Account Number Name CRM])&&
    'Table'[Month_Year]=MAX('Table'[Month_Year])&&
    'Table'[Date]=_date),[Value])

     

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

Hi Dizzy 
Following the sample file created by Anonymous, please try the following

Measure = 
SUMX ( 
    SUMMARIZE ( 'Table', 'Table'[Account Number Name CRM], 'Table'[Month_Year] ),
    VAR CurrentMonth =
        'Table'[Month_Year]
    VAR CurrentAccountTable = 
        FILTER ( 
            CALCULATETABLE ( 
                'Table',
                ALLEXCEPT ( 'Table', 'Table'[Account Number Name CRM] )
            ), 
            'Table'[Value] <> BLANK ( ) 
        )
    VAR FirstRecord = 
        TOPN ( 1, CurrentAccountTable, 'Table'[Date], ASC )
    VAR FirstMonth = 
        MAXX ( FirstRecord, 'Table'[Month_Year]  )
    VAR FirstValue = 
        MAXX ( FirstRecord, 'Table'[Value] )
    RETURN 
        IF ( CurrentMonth = FirstMonth, FirstValue )
)