Measure to extract month

[Anonymous]

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

[v-yanjiang-msft]

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]

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]

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.

[Anonymous]

Sorry @v-yanjiang-msft , but the measure didn't work because it's direct query 😞

[v-yanjiang-msft]

Hi @Anonymous ,

I modify the formula based on your sample data, you can have a try.

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

Get the result.

I attach the 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]

Hello @v-yanjiang-msft , 

My report is on direct query, it doesn't accept SUMX 

[Anonymous]

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

[Fowmy]

@Anonymous 

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

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

[Anonymous]

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

[Fowmy]

@Anonymous 

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