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 yellow values:

 

Best regrads

 

 

  • 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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Hi Dizzy 
    Please elaborate on your expected result perhaps on top of the example that you've provided.

    • Dizzy's avatar
      Dizzy
      Frequent Visitor
      2 years ago

      Hi tamerj1 

       

      the value what I expected are in yellow in sorted by Fiscal Month.

      The Last Row in my example is returned two values one in august and other one in january, I want to have at the end only august.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Dizzy's avatar
      Dizzy
      Frequent Visitor
      2 years ago

      Thank you.

      This is not why I'm locking for.

      In your example, it schould be only Feb 2024 for A and C Only Jan.

      Ony first value sorted by Month.

  • tamerj1's avatar
    tamerj1
    Community 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 )
)