Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure to extract month

Hello Community,

 

I am connected to a live dataset where I can't create calculated columns or go to the power query, I have a column date and I want to extract the month/Year from it. 

Is there any possibilty to create measure to extract this ?

 

Thanks in advance

 

Need Help

10 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    You can use a measure to extract the Month Year as follows:

    MonthYear = format( MAX('Date'[Date]) , "Mmm yyyy")

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for you response, but when I add a calculation with this measure it takes always the latest month as seen here :

       

       

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        Anonymous 

        May I know, based on the screenshot above, what your expected result for Month and Year ? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Fowmy Thanks for you help, I have 3 columns raw material number, Quantity and date, I want when selecting these 3 columns on a table, see the sum of quantity per material number per date for example:

    I have this data, I want the sum of quantity for 1002 on february, mars...

     

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    As measure calculated by the context, in the formula, the context is date, if you put date in the visual, the formula will return the current date in the same row, if you don't put date in the visual, the formula will return the max date in the table.

    My solution is put date in the visual, and create another measure to sum the value.

    SUM =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    ),
    'Table'[Value]
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello v-yanjiang-msft 

       

      Thanks for your effort. Unfortenatly this is not the expected result, I made some modification on your pbix

      file, I added a number column and another date in 2020,

       

      what I want to display is a table showing the sum of value per number and date for example:

      Number        Date             SUM

      1002             FEB 2020      55

      1002             FEB 2022      15

      1003             FEB 2020      40

      1003             FEB 2022      20

       

      I hope I was clear 

       

      Thanks in advance 🙂

                

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

        Does your expected result based on the data in the screen shot? In your screen shot, in my understanding, you want to get:

        Num   Date          Sum

        1002   2022-01        6

        1003   2022-02        15

        1002   2020-01        11

        1003   2020-01        15

        Best Regards,
        Community Support Team _ kalyj

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