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 a...
Need Help
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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello v-yanjiang-msft , 

       

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